Can I use updateCells to write data onto a row-CodePudding

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?

CodePudding user response：

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:

CodePudding user response：

It looks like you use the Sheets API, not the built-in SpreadsheetApp API. You can update a whole range of data at once:

let valueRange = Sheets.newValueRange();
valueRange.values = [
  [ 'Header 1', 'Header 2' ],
  [ 'Cell A2', 'Cell B2' ],
  [ 'Cell A3', 'Cell B3' ]
];
let spreadsheetId = 'h1K91cy4ewYJ7zaeMVREX8_S4QCUt_Db5CMWP0Gwiup2';
let rangeA1 = 'A1:B3';
let options = { valueInputOption: 'USER_ENTERED' };
let result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, rangeA1, options);