Vfrdtyky

I want to Update my Google sheets data via Google Sheets API.
It look like connect to Google's server and send request successful.
But my Google sheets data was not update.
And the response:result.updatedCells is undefined.
Where did I go wrong?

function listMajors(auth) {

    var body = {

        values: [["2"]]

    };

    sheets.spreadsheets.values.update({

        spreadsheetId: '{MyGoogleSheetsId}',

        range: "table2!K2"+"?valueInputOption=RAW",

        majorDimension:'ROWS',

        auth: auth,

        resource: body

    }, function(err, result) {

        if(err)

            console.log(err);

        else 

            console.log('updateRange: '+result.updatedRange);

            console.log('updatedCells: '+result.updatedCells);

            console.log('updatedRows '+result.updatedRows);

        });

    }

}

Value of response of range is OK, but response of other is undefined
Although server had received my request.
My Google sheets still not update.

How about this modification?

Modification points:

• Please put the property of majorDimension to the resource object.


• Please modify range: "table2!K2"+"?valueInputOption=RAW",
  


• You can retrieve the result by result.data.

Modified script:

function listMajors(auth) {

  var body = {

    majorDimension: "ROWS", // Added

    values: [["2"]],

  };

  sheets.spreadsheets.values.update({

    spreadsheetId: '{MyGoogleSheetsId}',

    range: "table2!K2", // Modified

    valueInputOption: "RAW", // Modified

    auth: auth,

    resource: body,

  }, function(err, result) {

    if (err) {

      console.log(err); // or console.log(err.errors);

      return;

    }

    console.log('updateRange: '+result.data.updatedRange); // Modified

    console.log('updatedCells: '+result.data.updatedCells); // Modified

    console.log('updatedRows '+result.data.updatedRows); // Modified

  });

}

Note:

• This modified script supposes the following points.
  
  
  
  
  • Your environment can update the spreadsheet using Sheets API.
  
  
  • 
    sheets is declared.
  
  
  
  


• If this didn't work, can you try it with the latest version of googleapis?

Reference:

• spreadsheets.values.update

In my environment, I confirmed that this modified script worked. If this didn't work in your environment, I'm sorry.

Edit:

In my environment, I confirmed that the modified script worked fine using googleapis of version 27.0.0 and the latest version (35.0.0). Unfortunately, I couldn't replicate your situation. So I would like to propose other method.

Sample script:

This script updates the spreadsheet using the method of values.update of Sheets API. This script uses auth of your script. When you use this script, please modify the script in listMajors(auth) to the following script.

function listMajors(auth) {

  const request = require('request');



  const spreadsheetId = '{MyGoogleSheetsId}';

  const range = "table2!K2";

  auth.getRequestHeaders()

  .then(function(e) {

    var body = {

      majorDimension: "ROWS",

      values: [["2"]],

    };

    var options = {

      uri: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?valueInputOption=RAW`,

      method: "put",

      headers: e,

      json: body,

    };

    request(options, function(err, res, result){

      if (err) {

        console.log(err);

        return;

      }

      console.log(result);

    });

  });

}