Google Sheets for Developers Sheets API append function appending row to first row of table instead-CodePudding

const { google } = require('googleapis');
const sheets = google.sheets('v4');
var request = {
    spreadsheetId: triggerDoc.parameters.spreadSheetId,
    range: sheetName   "!A:Z",
    insertDataOption: "INSERT_ROWS",
    responseDateTimeRenderOption: "SERIAL_NUMBER",
    responseValueRenderOption: "FORMATTED_VALUE",
    valueInputOption: "USER_ENTERED",
    "includeValuesInResponse": true,
    resource: {
        values: rowsToInsert
    },
    auth: oAuth2Client,
};
sheets.spreadsheets.values.append(request, function (err, response) {
    resp.spreadsheets = true;
    if (err) {
        console.log(err);
        return callback(err);
    }
    // console.log(response);
    return callback();
});

I am using the above code to append a set of values into the google sheet, the value is getting appended but instead of appending end, getting added to the first-line because the google sheet is not recognizing the table range correctly.

When I triggered the API from Here the value is not appended in the order of API triggered.

CodePudding user response：

When I tested the spreadsheet of your updated image, I could confirm the same issue with you. In this case, when the column "B" is the number, your issue occurred. When I modified the values of column "B" to the string values, I confirmed that the inputted values could be appended to the last row. So I thought that this might be a bug.

From the above situation, in order to achieve your goal, I would like to propose the following workaround.

Retrieve the last row number using sheets.spreadsheets.values.get().
Append the values to last row of the sheet using sheets.spreadsheets.values.update().

When this workaround is reflected to your script, it becomes as follows.

Modified script:

sheets.spreadsheets.values.get(
  {
    spreadsheetId: triggerDoc.parameters.spreadSheetId,
    range: sheetName   "!A:Z",
    auth: oAuth2Client,
  },
  (err, { data }) => {
    if (err) {
      console.log(err);
      return;
    }
    const row = data.values.length   1;
    var request = {
      spreadsheetId: triggerDoc.parameters.spreadSheetId,
      range: sheetName   "!A"   row,
      responseDateTimeRenderOption: "SERIAL_NUMBER",
      responseValueRenderOption: "FORMATTED_VALUE",
      valueInputOption: "USER_ENTERED",
      includeValuesInResponse: true,
      resource: {
        values: rowsToInsert,
      },
      auth: oAuth2Client,
    };
    sheets.spreadsheets.values.update(request, (err, { data }) => {
      if (err) {
        console.log(err);
        return;
      }
      console.log(data);
    });
  }
);

Modified script:

References: