Search code examples
google-sheetsgoogle-apps-scriptgoogle-sheets-api

Using Sheets.Spreadsheets.Values.update() Correctly

I've created an array that I'd like to have pasted on a sheet in cell A1. I'm aware of how to do that using setValues() but I was under the impression that using Sheets.Spreadsheets.Values.update() would be faster.

So I guess that's my first question. Is it faster? My array, we'll call updatedArr consists of 70K rows by 35 columns of data.

I just can't seem to figure out how to paste this array using that method. Here is my attempt:

Sheets.Spreadsheets.Values.update({ updatedArr }, tss.getId(), `'` + clickerDataSht.getName() + `'!A1`, { valueInputOption: "USER_ENTERED" });

The error I get:

GoogleJsonResponseException: API call to sheets.spreadsheets.values.update failed with error: Invalid JSON payload received. Unknown name "updatedArr" at 'data': Cannot find field.

Is there something I need to be doing to that array?

Solution

  • Modification points:

    • { updatedArr } is the same with { updatedArr: updatedArr }. If you want to use "Method: spreadsheets.values.update", the property name for the values is required to be values. I guessed that this might be the reason for your current issue of Unknown name "updatedArr" at 'data': Cannot find field..

    If your value of updatedArr is a 2-dimensional array, how about the following modification?

    From:

    Sheets.Spreadsheets.Values.update({ updatedArr }, tss.getId(), `'` + clickerDataSht.getName() + `'!A1`, { valueInputOption: "USER_ENTERED" });

    To:

    Sheets.Spreadsheets.Values.update({ values: updatedArr }, tss.getId(), `'${clickerDataSht.getName()}'!A1`, { valueInputOption: "USER_ENTERED" });
    • By this modification, the values updatedArr is put into cell "A1" of clickerDataSht.getName() sheet.

    Note:

    References: