Home > OS >  Google Sheets api Append and Change Colour of the row (Node js)
Google Sheets api Append and Change Colour of the row (Node js)

Time:04-14

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.

References:

  • Related