Search code examples
pythongoogle-sheetsgoogle-sheets-api

Updating Google Sheet with API & Python with less rows than original

I'm building a script that writes a deployment log for all our products. I have a Google sheet that holds the data. The first sheet of that spreadsheet is titled "Recent Releases" and as it says, holds the most recent releases (as of when the script runs). Most times there will be more or less rows than the current data. If there are less rows, how do I overwrite the extra existing rows?

I have this function:

def overWriteSheet(self, service, spreadSheetId, strRange, myData): 

    try: 
        sheet = service.spreadsheets()
        body={'values': myData}
        result = service.spreadsheets().values().update(spreadsheetId=spreadSheetId, range=strRange, valueInputOption='RAW', body=body).execute()
    except Exception as e: 
        print(e)

which works but only replaces up to the number of items in the passed in array in the body param. This leaves me with the extra old data in the sheet. I could:

  1. read the sheet first
  2. count the number of rows
  3. compare to the new data array
  4. if new data count is less than existing data add empty entries in the new data array

but that seems like a hack and very inefficient. Is there nothing I can pass to let the API know to clear the sheet first then add the new data?

Solution

  • About Is there nothing I can pass to let the API know to clear the sheet first then add the new data?, how about using "Method: spreadsheets.values.clear"? When this is reflected in your script, how about the following modification?

    Modified script:

    Please set your sheet name.

    I couldn't know the value of strRange. If this is the sheet name, I think that this value can be used to sheet.values().clear(spreadsheetId=spreadsheetId, range=sheetName).execute().

    def overWriteSheet(self, service, spreadSheetId, strRange, myData): 

    try: 
        sheet = service.spreadsheets()

        sheetName = "Sheet1" # Please set your sheet name.
        sheet.values().clear(spreadsheetId=spreadsheetId, range=sheetName).execute()

        body={'values': myData}
        result = sheet.values().update(spreadsheetId=spreadSheetId, range=strRange, valueInputOption='RAW', body=body).execute()
    except Exception as e: 
        print(e)

    • When this script is run, first, the sheet is cleared. And, the value is put into the sheet.

    • For example, when you want to keep the 1st header row, you can use sheetName = "'Sheet1'!A2:Z" instead of sheetName = "Sheet1". Z is the last column. If your last column is not Z, please modify this.

    Reference: