Introduction

How often do you have the opportunity to work with data in spreadsheet software such as SpreadSheet or Excel? It is expected that many people spend a lot of time in their work.

If you can manipulate chart data that takes a lot of time with Python and automate routine work, it will surely lead to operational efficiency. Now let's work with SpreadSheet in popular Python.

Overview

In this article, we will publish a Python Class for "reading / writing / erasing / formatting" to SpreadSheet in Google Drive.

There is already a convenient Python Lib called gspread in the streets, but let's try it for the following purposes.

--Get used to using Google API --Try changing the format through Request

This time, I will present an example of defining a Class, instantiating it, and using it. You can edit multiple SpreadSheets at the same time by instantiating, so please give it a try. Also, Class is just an example, so if you are interested, please customize it yourself.

Premise

--This article uses Jupyter Notebook to draw the table in Pandas Dataframe for visual clarity. However, it can also be used with regular Py files. --Please do the following article so that you can authenticate to Google Sheets API from Python --Download token.pickle from the official URL below and rename it to "spreadsheet_token.pickle" - https://developers.google.com/sheets/api/quickstart/python --If you want to check how to download the above token with explanation, please refer to the following URL. - https://qiita.com/ken0078/items/ece6fe2a871446383481

procedure

** 1. Prepare the following Spread Sheet ** This time, I prepared Sheets with values entered in columns A to E and rows 1 to 10. (WorkSheet name is'sample') スクリーンショット 2020-03-01 22.51.46.png

** 2. Create an ipynb in Jupyter Notebook and copy and execute the following Class in the step **

View code

import re
import time
import json
import copy
import pickle
import os.path

from pprint import pprint
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request


class SpreadSheetsApi:

    def __init__(self,SPREADSHEET_ID,RANGE_NAME):
        self.service = self.launch_api()
        self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)

    def launch_api(self):
        SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

        creds = None
        if os.path.exists('spreadsheet_token.pickle'):
            with open('spreadsheet_token.pickle', 'rb') as token:
                creds = pickle.load(token)
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(
                    'credentials.json', SCOPES)
                creds = flow.run_local_server(port=0)
            with open('spreadsheet_token.pickle', 'wb') as token:
                pickle.dump(creds, token)

        service = build('sheets', 'v4', credentials=creds)
        
        return service

    def read_sheet(self,SPREADSHEET_ID,RANGE_NAME):
        # Call the Sheets API
        sheet = self.service.spreadsheets()
        result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
        self.values = result.get('values', [])
        
        return self.values
        
    def write_values(self,SPREADSHEET_ID,RANGE_NAME,value_list):
        # Call the Sheets API(Overwrite Sheets)

        ValueInputOption = 'RAW'
        body = {
            'values': value_list,
        }
        result = self.service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,valueInputOption=ValueInputOption, body=body).execute()
        self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
        
    def clear_contents(self, SPREADSHEET_ID, sheet_num,start_row):
        # Call the Sheets API(Append Texts)

        ValueInputOption = 'USER_ENTERED'

        requests = []

        requests.append({
                "updateCells": {
                "range": {
                    "sheetId": sheet_num,
                    "startRowIndex": start_row
                },
                "fields": "userEnteredValue"
                }
            })

        body = { 'requests': requests }

        response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()
        self.values = self.read_sheet(SPREADSHEET_ID,RANGE_NAME)
    


    def change_format(self,SPREADSHEET_ID,sheet_num,value_list):
        # Call the Sheets API(Append Texts)
        ValueInputOption = 'USER_ENTERED'

        requests = []


        requests.append({
            "repeatCell": {
                "range": {
                    "sheetId": sheet_num,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": len(value_list[0]),
                    },
                "cell": {
                    "userEnteredFormat": {
                        "horizontalAlignment" : "CENTER",
                        "textFormat": {
                            "fontSize": 18,
                            "bold": True,
                            }
                        }
                    },
                "fields": "userEnteredFormat(textFormat,horizontalAlignment)"
                },
            })

        requests.append({
            "updateBorders": {
                "range": {
                "sheetId":sheet_num,
                "startRowIndex": 0,
                "endRowIndex": len(value_list),
                "startColumnIndex": 0,
                "endColumnIndex": len(value_list[0])
                },
                "top": {
                "style": "SOLID",
                "width": 1,
                },
                "bottom": {
                "style": "SOLID",
                "width": 1,
                },
                "right": {
                "style": "SOLID",
                "width": 1,
                },
                "right": {
                "style": "SOLID",
                "width": 1,
                },
                "innerHorizontal": {
                "style": "SOLID",
                "width": 1
                },
                "innerVertical": {
                "style": "SOLID",
                "width": 1
                },
            }
            }
        )

        requests.append({
            "autoResizeDimensions": {
                "dimensions": {
                "dimension": "COLUMNS",
                "startIndex": 0,
                "endIndex": len(value_list[0])
                }
            }
            })


        body = { 'requests': requests }

        response = self.service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()

** 3. Based on the defined Class, create an instance with SpreadSheet ID (str) in 1. above as an argument **

Spread Sheet ID is written in alphanumeric characters just before "/ edit" in the Spread Sheet URL.

spreadsheet_id ='XXXXXXXXXXX' #SpreadSheet ID defined as string
range_name ="sample" #Define the Worksheet name you want to extract
test_ss = SpreadSheetsApi(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name) #SpreadSheet/Create an instance to extract Worksheet values

As soon as you create this instance, the value of the target sheet will be extracted from Spread Sheet.

** 4. Convert Python List to Pandas Dataframe and check the read value ** At the same time you create this instance, the list of Elements (title / body / table, etc.) of all sheets will be extracted from SpreadSheet. In this example, the extracted values are stored in test_ss.values.

import pandas as pd
#Convert table of values to Pandas Dataframe
df = pd.DataFrame(test_ss.values,columns=['A','B','C','D','E'])
df.index = df.index + 1

It has the same content as Spread Sheet.

** 5. Rewrite Spread Sheet with the following values using the method ** I would like to reflect the example_value below in this table.

example_value = [["A1","B1"],["A2","B2"]] #Value to rewrite
test_ss.write_values(SPREADSHEET_ID = spreadsheet_id, RANGE_NAME = range_name,value_list =example_value) #Method for rewriting

In the Sheets API, a Sheet matrix is represented by a two-layer multiple list. The list in the first tier shows the rows of Sheets, and the list in the second tier is the columns.

** 6. Check if the rewritten value is reflected スクリーンショット 2020-03-01 23.06.43.png

Exactly, only columns A and B in rows 1-2 have been rewritten to the specified values.

** 7. Rewrite the format ** Next, let's rewrite not only the value but also the format with the following code.

test_ss.change_format(sheet_num=0, SPREADSHEET_ID = spreadsheet_id, value_list=test_ss.values) #Format rewriting method

The reason for taking value_list as an argument is to read the number of matrices to be edited.

** 8. Check if the rewritten format is reflected **

The format has been changed only where the value is entered.

This time, I applied a ruled line to the entire list and made the characters on the first line large and bold. If you want to format it as you like, please rewrite the request contents by referring to the Reference of Google Sheets API.

Edit Google Spread Sheets with Python (Low-cost RPA case with Google APIs and Python)

Introduction

Overview

Premise

procedure