Using Python to query SQL data and then auto update Google sheets

Question

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])

Lorena Gomez · Accepted Answer · 2023-01-27 18:32:14Z

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

Thankyou so much it works. I am able to update the sheet. Its just that the values that we entered manually are sample output. The entire data is recorded in data and there are several 1000s of rows of output of the SQL query. I want to insert the SQL output (sample shared above) to the Google sheet. 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 records_df: print(data[i])
@King If the Lorena's answer actually worked for you (at least for your initial concern), don't forget to either upvote or mark it as 'best answer'. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved! :D

tdy · Accepted Answer · 2023-01-31 18:50:29Z

0

I solved it 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)

edited Jan 31, 2023 at 18:50

tdy

42k42 gold badges124 silver badges125 bronze badges

answered Jan 28, 2023 at 18:05

King

415 bronze badges

1 Comment

King Over a year ago

I am able to update my sheet with the SQL output query but the values are text and not numbers. Can someone suggest how to typecast? def update_sheet(sheet, table, columns="ABCD", header=2): to_update = [] table = list(zip(*table)) for col_name, col in zip(columns, table): r = f"{col_name}{header}:{col_name}{len(col) + header}" cells = sheet.range(r) for cell, value in zip(cells, col): cell.value = value to_update.extend(cells) sheet.update_cells(to_update)

Collectives™ on Stack Overflow

Using Python to query SQL data and then auto update Google sheets

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related