Google Sheets api. Add values to empty cells in named range

Question

I have 3 values that I have generated in array, that I want to append to a named range in the empty cells, at the beginning of the named range. I want it to fill in those empty cells, not add the values at the end of the range, which it is doing now. Here is an example of the code. The Named range is called BSNID. Screenshots included as well. Thank you.

const pushDataUpdates = async (inData, pushRange) => {
    return new Promise((resolve, reject) => {
        sheets.spreadsheets.values.append({
            spreadsheetId,
            range: pushRange,
            valueInputOption: 'RAW',
            insertDataOption: 'OVERWRITE',
            includeValuesInResponse: true,
            resource: {
                majorDimension: 'COLUMNS',
                values: [inData]
            }
        }, (err,res) => {
            if (err) {
                reject(err)
            } else {
                console.log('Updating idList')
                //console.log(res);
                resolve();
            }
        })
    })
}
pushDataUpdates([1,2,3], 'BSNID')

Named Range

Column with empty cells at beginning

Answer 1

I believe your goal is as follows.

• You want to add [1,2,3] from the top cell in the named range of "BSNID" using googleapis for Node.js.
• You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, how about the following modification?

Modified script:

From:

sheets.spreadsheets.values.append({
    spreadsheetId,
    range: pushRange,
    valueInputOption: 'RAW',
    insertDataOption: 'OVERWRITE',
    includeValuesInResponse: true,
    resource: {
        majorDimension: 'COLUMNS',
        values: [inData]
    }
}, (err,res) => {

To:

sheets.spreadsheets.values.update(
  {
    spreadsheetId,
    range: pushRange,
    valueInputOption: "RAW",
    resource: {
      majorDimension: "COLUMNS",
      values: [inData],
    },
  }, (err, res) => {

• By this modification, when your sample image is used, the values of [1,2,3] are put to the top 3 cells, respectively.

Reference:

• Method: spreadsheets.values.update