Append Inserting in Random Rows

Question

I'm inserting data into sheets using append, but the new data ends up being inserted in random positions. Sometimes at the bottom, sometimes at the top. Apparently sometimes in the middle also, although I've not witnessed that myself yet.

I think it's caused when sheet users have added filtering on the sheet. When a filter is active on the sheet, it appears to affect where the new data is inserted on the sheet, although I'm not 100% certain that this is the case.

There is no sorting on the sheet.

Any idea how to ensure new entries are only appended to the bottom always?

const result = await sheets.spreadsheets.values.append({
            spreadsheetId: spreadsheetId,
            range: range,
            valueInputOption: "RAW",
            insertDataOption: "INSERT_ROWS",
            resource: {
                values: data
            }
        });

For the above, range is SheetName!A:A so I'm not specifying any row.

Tanaike · Accepted Answer · 2024-05-05 00:42:34Z

Although I'm not sure about your actual Spreadsheet, in your situation, how about the following modification?

Modified script:

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetName = "Sheet1"; // Please set your sheet name.
const data = [["sample1", "sample2", "sample3"]]; // Please set your 2 dimensional array.

const sheets = google.sheets({ version: "v4", auth });
const res = await sheets.spreadsheets.values.get({ spreadsheetId, range: sheetName });
const lastRow = res.data.values.length;
const result = await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: `'${sheetName}'!A${lastRow + 1}`,
  valueInputOption: "RAW",
  resource: { values: data },
});
console.log(result.data);

In this modification, as the first step, the values are retrieved from the sheet using "spreadsheets.values.get". And, the last row number is retrieved from the values. And, data is put into the next row of the last row using "spreadsheets.values.update".

Collectives™ on Stack Overflow

Append Inserting in Random Rows

1 Answer 1

Modified script:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related