Copy background color from one column to another with nodejs google sheets api

Question

I am trying to copy the background color from one column that already has conditional formatting to another column so that both column cells have the same background color even though they have different data.

I found this php doc that has get and set background: https://developers.google.com/resources/api-libraries/documentation/sheets/v4/php/latest/class-Google_Service_Sheets_CellFormat.html

But can't find a ton of info on how to format this request with node. Not sure if get and set background exists in node? https://github.com/googleapis/google-api-nodejs-client/tree/master/samples/sheets

My plan was to get the background color from the formatted column and then copy that formatting into the column with no formatting. I can't really find any documentation on this for Node. Is there any? Is this even possible with a batchUpdate request?

function getFormatting(sheetId, startRowIndex, endRowIndex, columns, column) { const function_name = 'getFormatting';

let { startColumnIndex, endColumnIndex } = getStartEndIndex(columns, column, column);

const request =  [{
  "getBackgroundColor": {
          "backgroundColorRange": {
          "range": {
            sheetId,
            startRowIndex,
            endRowIndex,
            startColumnIndex,
            endColumnIndex,
          }, }
  }
}];

}

I am hoping to get the background colors for say Column A1:A1(length) and copy those to B1:B1(length) in exactly the same order on the rows

The background colors of cells can be copied using Sheets API. At first, can I ask you about your question? 1. Can I ask you whether you have already been able to get and put the values for the Spreadsheet using Sheet API by googleapis with Node.js? 2. What is length of Column A1:A1(length) and B1:B1(length)? 3. Your question to know the script for copying the background color of cell "A1" to the cell "B1". Is my understanding correct? — Tanaike
Yes I am trying to figure out how to copy the background colors of of column A to column B. Column A has conditional formatting already based on text in the cell. I can't do the same conditional formatting on column B because there are too many values AND I need each row in B to match the colors in the same row in A. I have been able to use the node api to copy sheets from one spreadsheet to another, and do deleting, add color backgrounds with conditional formatting, change column size, but cant figure out how to copy colors from one column to another. Thanks for any ideas or documentation! — rommims
2. How many rows there are is different every time. So one day it could be A1:A25, another day it could be A1:A3500. The A and B will have the same length(number of rows) so when its A1:A500 it will also be B1:B500 — rommims
Thank you for replying. I have to apologize for my poor English skill. From your replying, I have one more question. Do you want to copy the conditional formatting role from the column "A" to "B"? Or do you want to copy only the background colors from the column "A" to "B"? Can I ask you about your goal? — Tanaike
Thanks for answering! Only the background color. Column A Row 10 has 'test1' string in the cell - conditional formatting will turn it green. Column B Row 10 will have '44' string in the cell and will turn green IF Column A Row 10 cell is green. — rommims

Tanaike Tanaike · Accepted Answer · 2019-10-31T23:51:05

You want to copy the background colors of the cells in the column "A" to the column "B".
You want to achieve this using googleapis with Node.js.
You have already been able to get and put values for Spreadsheet using Sheets API.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Flow:

The flow of this script is as follows.

Retrieve the background colors of the cells in the column "A" using spreadsheets.get().
Create the request body using the retrieved values.
Change the background colors of the cells in the column "B" using spreadsheets.batchUpdate().

Sample script:

Before you use this, please set the variables of spreadsheetId and sheetName.

const sheets = google.sheets({ version: "v4", auth });

const spreadsheetId = "###"; // Please set the Spreadsheet ID.
const sheetName = "Sheet1"; // Please set the sheet name. In this sample, "Sheet1" is set.

const request = {
  spreadsheetId: spreadsheetId,
  ranges: [`${sheetName}!A1:A`],
  fields: "sheets"
};
sheets.spreadsheets.get(request, function(err, response) {
  if (err) {
    console.error(err);
    return;
  }
  let requests = {
    requests: [
      {
        updateCells: {
          fields: "userEnteredFormat.backgroundColor",
          start: {
            sheetId: response.data.sheets[0].properties.sheetId,
            rowIndex: 0,
            columnIndex: 1
          },
          rows: response.data.sheets[0].data[0].rowData.map(function(row) {
            return {
              values: [
                {
                  userEnteredFormat: {
                    backgroundColor:
                      row.values[0].effectiveFormat.backgroundColor
                  }
                }
              ]
            };
          })
        }
      }
    ]
  };
  sheets.spreadsheets.batchUpdate(
    { spreadsheetId: spreadsheetId, requestBody: requests },
    function(err, response) {
      if (err) {
        console.error(err);
        return;
      }
      console.log(JSON.stringify(response.data, null, 2));
    }
  );
});

Copy background color from one column to another with nodejs google sheets api

1 Answers

Flow:

Sample script:

References: