Google Sheets API v4 write data

Question

I am trying to write data to a Google Sheet using the Nodejs API v4. I can sucessfully read and clear data, so I have set everything up correctly. However, using the example in the docs here for the update method, I cannot work out how to specify the data I want to put in the Google sheet. I would like an example showing how to paste the data held by const data into the sheet specified. Here is what I have so far:

const data = [ [1, 2], [3, 4], ]; const auth = new google.auth.GoogleAuth({ scopes: ["https://www.googleapis.com/auth/spreadsheets"], }); const authClient = await auth.getClient(); const sheets = google.sheets({ version: "v4", auth: authClient }); const request = { spreadsheetId: "my-spreadsheet-id", range: "Sheet1!A:E", valueInputOption: "", resource: {}, auth: authClient, }; const response = await sheets.spreadsheets.values.update(request);

ziganotschka · Accepted Answer · 2020-05-14 10:46:50Z

You must specify a value input option

Possible values are:

USER_ENTERED
RAW
INPUT_VALUE_OPTION_UNSPECIFIED

The resource is your data array.

Sample:

const request = { spreadsheetId: "my-spreadsheet-id", range: "Sheet1!A:E", valueInputOption: "USER_ENTERED", resource: {values: [ [1, 2], [3, 4], ]}, auth: authClient, };

digibake · Accepted Answer · 2022-02-03 20:48:28Z

To add to ziganotschka's answer above. It looks like request.resource is now outdated in v4 as of around Feb 2020 and it's now necessary to use request.requestBody instead.

Updated Sample:

const request = { spreadsheetId: "my-spreadsheet-id", range: "Sheet1!A:E", valueInputOption: "USER_ENTERED", requestBody: {values: [ [1, 2], [3, 4], ]}, auth: authClient, };

Collectives™ on Stack Overflow

Google Sheets API v4 write data

2 Answers 2

You must specify a value input option

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You must specify a value input option

Comments

Comments

Related