使用Node.js Google Sheets API将背景色从一列复制到另一列 [英] 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.

我发现这个具有获取并设置背景的php文档: https://开发人员. google.com/resources/api-libraries/documentation/sheets/v4/php/latest/class-Google_Service_Sheets_CellFormat.html

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

但是找不到有关如何使用node格式化此请求的大量信息.不确定节点中是否存在获取和设置背景? https://github.com/googleapis/google- api-nodejs-client/tree/master/samples/sheets

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

我的计划是从带格式的列中获取背景色，然后将该格式复制到无格式的列中. 我真的找不到有关Node的任何文档.有没有? 甚至可以通过batchUpdate请求吗?

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';

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,
          }, }
  }
}];

}

我希望获取A1:A1列(长度)的背景色，并以与行中完全相同的顺序将其复制到B1:B1(长度)

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

推荐答案

• 您要将"A"列中单元格的背景色复制到"B"列中.
• 您要使用带有Node.js的googleapis实现此目的.
• 您已经能够使用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.

此脚本的流程如下.

1. 使用spreadsheets.get()检索"A"列中单元格的背景色.
2. 使用检索到的值创建请求正文.
3. 使用spreadsheets.batchUpdate()更改"B"列中单元格的背景颜色.
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().

示例脚本:

在使用此功能之前，请设置spreadsheetId和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));
    }
  );
});

参考文献:

• 方法:sheetssheets.get
• 方法:sheetssheets.batchUpdate
• UpdateCellsRequest

References:

• Method: spreadsheets.get
• Method: spreadsheets.batchUpdate
• UpdateCellsRequest

这篇关于使用Node.js Google Sheets API将背景色从一列复制到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！