Can I update cells without getting their current values first using gspread

Question

I have a google sheet that I am inserting/updating the values of based on the results of some API calls I am making in a python script.

Following the gspread documentation, I'm currently fetching the range from the sheet, then updating the values, then writing it back.

But I don't care about the values in the sheet, I would rather write blindly to it - saving me precious API read quota.

Is there a way I can write to the cells without reading them first?

I've googled around for anyone having the same problem and I can't find anywhere that tells me how I can do this (or even if I can at all)

Current code:

(rowData is the data I have gathered previously in the python script that I now want to write to the gsheet)

try:
        rowToUpdate = sheet.range("A"+str(index)+":Q"+str(index))
except Exception as e:
        print("Couldn't fetch rowToUpdate, error message was: ")
        print(repr(e))

try:
        for cell, value in zip(rowToUpdate, rowData):
            cell.value = value
        sheet.update_cells(rowToUpdate)

Ideally I would just do something like:

    try:
        for cell, value in zip(rowToUpdate, rowData):
            cell.value = value
        sheet.update_cells(rowToUpdate)```

Philippe Oger Philippe Oger · Accepted Answer · 2019-05-08T13:50:08

You could do it using the Sheetfu library (I'm the author): https://github.com/socialpoint-labs/sheetfu

The following code will just set the values without actually getting the values first:

from sheetfu import SpreadsheetApp as sa

spreadsheet = sa('path/to/secret.json').open_by_id('<insert spreadsheet id here>')
sheet = spreadsheet.get_sheet_by_name('<insert the sheet name here>')

# make sure your data is already in a 2D matrix form
input_data = [[1,2], [3,4]]

data_range = sheet.get_range(
    row=1, 
    column=1, 
    number_of_row=len(input_data), 
    number_of_column=len(input_data[0])
)

data_range.set_values(input_data)

This code will not query the sheet and gets the current values. It will only set the values using your data.

Can I update cells without getting their current values first using gspread

1 Answers