Google Sheets API NodeJS - Update cell without changing format

Question

Have written NodeJS code to Insert a Row in a Google Sheet using the API and updating the row values.

I insert the row with inheritFromBefore: true, which is working fine (I tested it by commenting out the updateOption). But while updating the cell the original format is not getting preserved; rather it is changing to default blank format.

function getSheetRow(jobData) {

    const jobID = jobData.getJobID();
    const jobName = jobData.getJobName();
    const company = jobData.getCompany();
    const pmName = jobData.getPMName();

    let rowData = [
        { userEnteredValue: { stringValue: jobID } },
        { userEnteredValue: { stringValue: jobName + " (" + company + ", " + pmName + ")" } }
    ];

    return rowData;
}


async function googleSheetInsertRow(workbookID, sheetNum, insertRowIndex, jobData) {
    let sheetIdMap = await googleSheetsGetSheetIDTitle(workbookID);
    let rowData = getSheetRow(jobData);
    return new Promise((resolve, reject) => {

        const insertOption = {
            insertDimension: {
                range: {
                    sheetId: sheetNum,
                    dimension: 'ROWS',
                    startIndex: insertRowIndex,
                    endIndex: insertRowIndex + 1
                },
                inheritFromBefore: true
            }
        };

        const updateOption = {
            updateCells: {
                rows: [{
                    values: rowData
                }],
                fields: '*',
                start: {
                    sheetId: sheetNum,
                    rowIndex: insertRowIndex,
                    columnIndex: 0
                }
            }
        };

        getGSheetsAPI().spreadsheets.batchUpdate({
            spreadsheetId: workbookID,
            resource: {
                requests: [
                    insertOption, updateOption
                ]
            }
        },
            (er, re) => {
                if (er) {
                    console.log(`Unable to insert new row into ${googleSheetName} : https://docs.google.com/spreadsheets/d/${workbookID}`);
                    return reject(er);
                }
                else {
                    console.log(`New row inserted into ${googleSheetName} : https://docs.google.com/spreadsheets/d/${workbookID}`);
                    return resolve();
                }
            }
        );
    });
}

[In the picture below] I have inserted a row in between Row 6 & 8. And Row 7 is inheriting the format from the row before, but while updating the cell value it is not getting preserved.

Looking for help. Thanks.

Answer 1

I think that in your issue, when userEnteredValue or userEnteredValue.stringValue is used to fields, the issue can be resolved. When you want to use the values except for stringValue, I would like to recommend to use userEnteredValue as the fields.

From:

const updateOption = {
    updateCells: {
        rows: [{
            values: rowData
        }],
        fields: '*',
        start: {
            sheetId: sheetNum,
            rowIndex: insertRowIndex,
            columnIndex: 0
        }
    }
};

To:

const updateOption = {
  updateCells: {
    rows: [
      {
        values: rowData,
      },
    ],
    fields: "userEnteredValue",  // <--- Modified
    start: {
      sheetId: sheetNum,
      rowIndex: insertRowIndex,
      columnIndex: 0,
    },
  },
};

Reference:

• UpdateCellsRequest