How can I append text to a specific column with JavaScript?-CodePudding

I am attempting to call a function that appends a specific column in my google sheet with the current date. Here's my code:

https://codepen.io/afjordan/pen/LYBRGPz?editors=0010

    //update tasks
    function fv1Passivated() {
      var in_date = new Date();
      var str = in_date.getDate()  '/' (in_date.getMonth() 1) '/' in_date.getFullYear()
  
      var params = {
          spreadsheetId: SHEET_ID,
          range: 'FV1!C:C', 
          valueInputOption: 'RAW',
      };

      var valueRangeBody = {
        
          "values": [
             [str]
          ]
      };

      console.log(valueRangeBody);
      var request = gapi.client.sheets.spreadsheets.values.append(params, valueRangeBody);
      request.then(function(response) {
        console.log(response.result);
      }, function(reason) {
        console.error('error: '   reason.result.error.message);
      });
    }

And here's what it's executing:

Debug Console

As you can see, it's appending the date to the end of the first available column instead of column C. This is indeed occurring in the sheet itself. What needs to be changed herein order to control what column gets appended?

Tried the above as described, but I also tried different values/formats for the designated range and couldn't get the data to append anywhere but the first column.

CodePudding user response：

It seems like you are not passing the parameters correctly, could you try passing the parameters object directly?

gapi.client.sheets.spreadsheets.values.append({
      "spreadsheetId": "YourSheetId",
      "range": "FV1!C:C",
      "valueInputOption": "RAW",
      "resource": {
        "values": [
          [
            "This is a test"
          ]
        ]
      }
    })
    .then(function(response) {
         // Handle the results here (response.result has the parsed body).
         console.log("Response", response);
       },
       function(err) { console.error("Execute error", err); 
    });

CodePudding user response：

I believe your goal is as follows.

You want to put a value of str to the next row of the last row in column "C" of "FV1" sheet.
You want to achieve this using googleapis for Javascript.
You have already been able to get and put values for the Spreadsheet using Sheets API.

In the current stage, it seems that gapi.client.sheets.spreadsheets.values.append puts the value to the next row of the last row in the data range. In order to achieve your goal, how about the following flow?

Retrieve values from column "C".
Retrieve the last row from the retrieved values.
Put the value using gapi.client.sheets.spreadsheets.values.update with the retrieved last row of the column "C".

When this flow is reflected in your script, how about the following modification?

Modified script:

var SHEET_ID = "###"; // Please set your spreadsheet ID.

gapi.client.sheets.spreadsheets.values.get({ spreadsheetId: SHEET_ID, range: 'FV1!C:C' }).then(({ result }) => {
  var row = result.values.length   1;
  var in_date = new Date();
  var str = in_date.getDate()   '/'   (in_date.getMonth()   1)   '/'   in_date.getFullYear()
  var params = {
    spreadsheetId: SHEET_ID,
    range: `FV1!C${row}`,
    valueInputOption: 'RAW',
  };
  var valueRangeBody = { "values": [[str]] };
  console.log(valueRangeBody);
  var request = gapi.client.sheets.spreadsheets.values.update(params, valueRangeBody);
  request.then(function (response) {
    console.log(response.result);
  }, function (reason) {
    console.error('error: '   reason.result.error.message);
  });
}, reason => {
  console.error('error: '   reason.result.error.message);
});

When this script is run, the value of str is put into the next row of the last row of the column "C" of "FV1" sheet.

Modified script:

References: