I have been trying format sheets row while appending, but can't figure it out. I have no problem with appending the rows, it works fine. So how can I append and format the row simultaneously ? From what I know I should be using "batchUpdate()" function, I tried in many ways but still can't figure it out.
CodePudding user response:
I believe your goal is as follows.
- You want to append a row to a sheet in a Google Spreadsheet.
- When the row is appended, you want to set the color (background color?) to the row.
- You want to achieve this using Node.js.
In this case, how about the following sample script?
Sample script:
In this script, googleapis for Node.js is used. Ref This can be used with Quickstart. Ref
const sheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.
const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetId = "0"; // Please set your sheet ID.
const appendValue = ["sample1", "sample2", "sample3"]; // This is a sample append value.
const backgroundColor = { red: 1, green: 0, blue: 0 }; // This is a sample background color. In this case, the red color is used.
const values = appendValue.map((e) => ({userEnteredValue: { stringValue: e }, userEnteredFormat: { backgroundColor }}));
const requests = [{appendCells: {rows: [{ values }], sheetId, fields: "userEnteredValue,userEnteredFormat"}}];
await sheets.spreadsheets.batchUpdate({spreadsheetId, resource: { requests }});
- When this script is run, a row of
"sample1", "sample2", "sample3"
is appended to the sheet, and the background color of the cells of the appended row is set as the red color.
Note:
- This is a simple sample script. So, please modify this for your actual situation.