Can I use updateCells to write data onto a row

Question

I want to put data onto sheet. I could use this direct method mention here

instead I am using

fetch(endpoint + '/' + id + '/:batchUpdate',
  method: method,
    headers: {
      'Accept': 'application/x-www-form-urlencoded',
      'Authorization': 'Bearer ' + access_token,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(body)
)

body = { 
  'requests': [
    'updateCells': {
      'rows': [{
        'values': [{ 'userEnteredValue': { 'stringValue': 'ID' } }],
        'values': [{
          'userEnteredValue': { 'stringValue': 'Name' },
          'userEnteredValue': { 'stringValue': 'Surname' },
          'userEnteredValue': { 'stringValue': 'Phone Number' },
          'userEnteredValue': { 'stringValue': 'Whatsapp Number' },
          'userEnteredValue': { 'stringValue': 'Email' },
          'userEnteredValue': { 'stringValue': 'Location' },
          'userEnteredValue': { 'stringValue': 'Date' },
          'userEnteredValue': { 'stringValue': 'Completed' }
        }],
      }],
      'fields': 'userEnteredValue',
    //'start': {
    //  "sheetId": 0,
    //  "rowIndex": 0,
    //  "columnIndex": 0
    //}
      'range': {
        "sheetId": 0,
        "startRowIndex": 0,
        "startColumnIndex": 0,
        "endColumnIndex": 8
      }
    }
  ]
}

I can only seem to update the first cell in the sheet with the data 'Completed'

so can I use updateCells?

Answer 1

I believe your goal is as follows.

• You want to put the values to the sheet of Google Spreadsheet using the batchUpdate method of Sheets API.
• You want to achieve this using the fetch API of Javascript.
• Your access token can be used for putting the values to Spreadsheet using Sheets API.

When I saw your request body, I thought that it is required to modify it. In your request body, the same key of userEnteredValue is used. By this, it is considered that only 'userEnteredValue': { 'stringValue': 'Completed' } is used. So, in this case, how about the following modification?

Modified script 1:

In this modification, your script is modified.

const access_token = "###"; // Please set your access token.
const id = "###"; // Please set your Spreadsheet ID.
const method = "POST";

const body = {
  requests: [
    {
      updateCells: {
        rows: [
          {
            values: [
              { userEnteredValue: { stringValue: 'ID' } },
              { userEnteredValue: { stringValue: 'Surname' } },
              { userEnteredValue: { stringValue: 'Phone Number' } },
              { userEnteredValue: { stringValue: 'Whatsapp Number' } },
              { userEnteredValue: { stringValue: 'Email' } },
              { userEnteredValue: { stringValue: 'Location' } },
              { userEnteredValue: { stringValue: 'Date' } },
              { userEnteredValue: { stringValue: 'Completed' } },
            ]
          }],
        fields: "userEnteredValue",
        range: { sheetId: 0 }
      }
    }]
};
const endpoint = "https://sheets.googleapis.com/v4/spreadsheets";
fetch(endpoint + '/' + id + '/:batchUpdate', {
    method: method,
    headers: {
      'Authorization': 'Bearer ' + access_token,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(body)
  }
)

• When you want to put the values from the cell "A1" of the sheet ID 0, range: { sheetId: 0 } can be used as the range.

Modified script 2:

In this modification, the prepared values are used. In this case, from your script, the string values are used. Please be careful about this.

const access_token = "###"; // Please set your access token.
const id = "###"; // Please set your Spreadsheet ID.
const method = "POST";

const values = [["ID", "Surname", "Phone Number", "Whatsapp Number", "Email", "Location", "Date", "Completed"], ["value1", "value2", "value3", "value4", "value5", "value6", "value7", "value8"]];
const body = { requests: [{ updateCells: {
  rows: values.map(r => ({ values: r.map(c => ({ userEnteredValue: { stringValue: c } })) })),
  fields: "userEnteredValue",
  range: { sheetId: 0 }
} }] };

const endpoint = "https://sheets.googleapis.com/v4/spreadsheets";
fetch(endpoint + '/' + id + '/:batchUpdate', {
    method: method,
    headers: {
      'Authorization': 'Bearer ' + access_token,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(body)
  }
)

Modified script 3:

In your situation, when "Method: spreadsheets.values.update" is used, the sample script is as follows.

const access_token = "###"; // Please set your access token.
const id = "###"; // Please set your Spreadsheet ID.
const method = "PUT";
const range = "Sheet1"; // In this sample, the values are put to "Sheet1".
const values = [["ID", "Surname", "Phone Number", "Whatsapp Number", "Email", "Location", "Date", "Completed"], ["value1", "value2", "value3", "value4", "value5", "value6", "value7", "value8"]];

const endpoint = "https://sheets.googleapis.com/v4/spreadsheets";
fetch(endpoint + '/' + id + '/values/' + range + "?valueInputOption=USER_ENTERED", {
    method: method,
    headers: {
      'Authorization': 'Bearer ' + access_token,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({values})
  }
)

References:

• Method: spreadsheets.batchUpdate
• UpdateCellsRequest
• Method: spreadsheets.values.update