CODEWITHSUNDEEP
google-sheets-api
rommims
rommims

Reputation: 13

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

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

Upvotes: 1

Views: 1557

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • 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.

  1. Retrieve the background colors of the cells in the column "A" using spreadsheets.get().
  2. Create the request body using the retrieved values.
  3. 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));
    }
  );
});

References:

Upvotes: 2

Related Questions