Revision history [back]

Here is a sample code to utilize spreadsheets.values.batchUpdate with NodeJS and the 'googleapis' module in Google Cloud Functions:

1. First, you need to install the 'googleapis' module:

npm install googleapis

1. Then, you need to authenticate with the Google Sheets API by creating credentials and saving the JSON file.

2. In your code, import the 'googleapis' module and create a client instance:

const {google} = require('googleapis');

const auth = new google.auth.GoogleAuth({
  keyFile: 'YOUR_CREDENTIALS.json',
  scopes: [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
  ]
});

const sheetsApi = google.sheets({version: 'v4', auth});

1. Use the spreadsheets.values.batchUpdate method to update the values in a sheet:

async function batchUpdateValues(spreadsheetId, range, values) {
  const request = {
    spreadsheetId,
    resource: {
      valueInputOption: 'USER_ENTERED',
      data: [
        {
          range,
          values
        }
      ]
    }
  };

  try {
    const response = await sheetsApi.spreadsheets.values.batchUpdate(request);
    console.log(`Updated ${response.data.totalUpdatedCells} cells.`);
  } catch (err) {
    console.error(err);
  }
}

1. Finally, call the batchUpdateValues function with the spreadsheet ID, range, and values you want to update:

const spreadsheetId = 'YOUR_SPREADSHEET_ID';
const range = 'Sheet1!A1:B2';
const values = [
  ['New value 1', 'New value 2'],
  ['New value 3', 'New value 4']
];

batchUpdateValues(spreadsheetId, range, values);

This will update the cells in the specified range with the new values.