I wanted to operate google spreadsheet from python, so I summarized the procedure to authenticate google and add it to the cell.
Create a google apis service account by using the URL below. Authenticate with service account and get data from Google Spreadsheet
Install the modules ʻoauth2clientandgspread` for google api authentication and spreadsheet manipulation.
$ pip3 install oauth2client
$ pip3 install gspread
Create an intersection class so that it can be used by multiple classes.
google_service_account.py
import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('path/to/google-service-account.json'), scope)
client = gspread.authorize(credentials)
google_spreadsheet_service.py
from datetime import date
from google_service_account import client
class GoogleSpreadsheetService:
@classmethod
def edit_cells(self):
spreadsheet = client.open('sample') #Specify the name of the spreadsheet you want to work with
worksheet = spreadsheet.worksheet('Annual bonuses') #Specify the sheet
data = ['{0:%Y-%m-%d}'.format(date.today()), Hello, World] #Data you want to insert
worksheet.append_row(data) #Insert data in last line
All you have to do is call GoogleSpreadsheetService.edit_cells () when you want to insert it.
I formatted it according to the Date format so that it would be treated as a date, but the inserted date data was treated as just a character string.
before
data = ['{0:%Y-%m-%d}'.format(date.today()), Hello, World]
worksheet.append_row(data) #It will be treated as just a character string
This was avoided by specifying value_input_option ='USER_ENTERED'.
after
data = ['{0:%Y-%m-%d}'.format(date.today()), Hello, World]
worksheet.append_row(data, value_input_option='USER_ENTERED')
Recommended Posts