I am trying to use Python to query MY SQL DB and then update the results in a Google Sheet. I have done all the steps required for setting up GC console and am able to access the sheet. Successfully connected to SQL DB as well.
['2023-01-18', '5231', 'ABC', 6164368.0, 234712.0, 108554.0]
['2023-01-17', '5231', 'ABC', 6434784.0, 231475.0, 94775.0]
['2023-01-16', '5232', 'ABC', 6463684.0, 232513.0, 100168.0]
I want to:
- a) Update the same in my Google Sheet - Sheet1 using Python.
- b) Add header columns like
Date,ID,Name,OrderValue,PurchaseValue,Clicks. - c) The sheets needs to be refreshed daily with new data, so before inserting values, I need to clear sheet content whenever the python code executes.
PFB the code snippet
cursor.execute(SELECT_QUERY)
result = cursor.fetchall()
df_columns = [x[0] for x in cursor.description]
i = 0
data = {i: list(r[0:]) for i, r in enumerate(result)}
for i in data:
print(data[i])
CodePudding user response：
To give you an idea, you can try with the following code, I added the authentication part in case you need it. You can adjust it to your needs but it basically does what you asked above. For more details I added some comments in the code. If you have any questions, let me know.
from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from pprint import pprint
# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/drive.file', 'https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/spreadsheets' ]
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = 'SpreadsheetID'
def main():
creds = None
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
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)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
try:
service = build('sheets', 'v4', credentials=creds)
# Call the Sheets API and the sheet ID
#Clear content in Sheet1!A1:F
call_sheet_with_format = service.spreadsheets().get(spreadsheetId=SAMPLE_SPREADSHEET_ID).execute()
sheets_with_format = call_sheet_with_format.get('sheets', '')
sheet_id = sheets_with_format[0].get("properties", {}).get("sheetId", 0)
request_body = {
'requests': [
{
'updateCells': {
'range': {
'sheetId': sheet_id
},
'fields':'userEnteredFormat'
}
}
]
}
request = service.spreadsheets().batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=request_body).execute()
request_clear = service.spreadsheets().values().clear(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A1:F").execute()
# Call the Sheets API and the sheet ID
#Adding headers to Sheet1
call_sheet_with_format = service.spreadsheets().get(spreadsheetId=SAMPLE_SPREADSHEET_ID).execute()
sheets_with_format = call_sheet_with_format.get('sheets', '')
sheet_id = sheets_with_format[0].get("properties", {}).get("sheetId", 0)
headers = "Date, ID, Name, OrderValue,PurchaseValue, Clicks"
request_body = {
"requests": [
{
"insertRange": {
"range": {
"sheetId": sheet_id,
"startRowIndex": 0,
"endRowIndex": 1
},
"shiftDimension": "ROWS"
}
},
{
"pasteData": {
"data": headers,
"type": "PASTE_NORMAL",
"delimiter": ",",
"coordinate": {
"sheetId": sheet_id,
"rowIndex": 0,
}
}
}
]
}
request = service.spreadsheets().batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=request_body)
response = request.execute()
pprint(response)
#Adding the data that you retrieved from the database
#For this to work, you need the data in the format below in 'records_df'
records_df = [['2023-01-18', '5231', 'ABC', 6164368.0, 234712.0, 108554.0], ['2023-01-17', '5231', 'ABC', 6434784.0, 231475.0, 94775.0], ['2023-01-16', '5232', 'ABC', 6463684.0, 232513.0, 100168.0]]
range_db = "Sheet1!A2:F"
value_input_option = "USER_ENTERED"
insert_data_option = "INSERT_ROWS"
value_range_body = {
"majorDimension": "ROWS",
"values": records_df,
}
request_2 = service.spreadsheets().values().append(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=range_db, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body)
response = request_2.execute()
except HttpError as err:
print(err)
if __name__ == '__main__':
main()
References:
- Python quickstart
- Method: spreadsheets.values.clear
- batchUpdate requests: InsertRangeRequest, PasteDataRequest
- Method: spreadsheets.values.append
CodePudding user response：
I solved it by this way, there are 2 issues. if we update row-wise, it will exhaust the Google API limit, so we must update column-wise and transpose it. Here I wanted to update Columns A-VPFB the code snippet.
def update_sheet(sheet, table, columns="ABCDEFGHIJKLMNOPQRSTUV", header=2):
to_update = []
table = list(zip(*table)) # transpose the table
for col_name, col in zip(columns, table): # iterating over columns now
r = f"{col_name}{header}:{col_name}{len(col) header}" # changed range
# print(r) # for debugging
cells = sheet.range(r)
for cell, value in zip(cells, col):
cell.value = value
to_update.extend(cells)
sheet.update_cells(to_update)