是否可以使用setValue和setNumberFormat中的Google表格api v4进一步降低请求的成本? [英] Is it possible to further reduce the cost of the request with Google sheets api v4 in setValue and setNumberFormat?

问题描述

现在，我使用下面的代码来格式化和插入值.

Now I use the code below to format and insert values.

function AddName () {
  const range1 = ['Q4:Q','S4:S','U4:U','W4:W','Y4:Y','AA4:AA','AC4:AC','AE4:AE',
  'AG4:AG','AI4:AI','AK4:AK','AM4:AM','AO4:AO','AQ4:AQ','AS4:AS','AU4:AU','AW4:AW','AY4:AY','BA4:BA',
  'BC4:BC','BE4:BE','BG4:BG','BI4:BI','BK4:BK','BM4:BM','BO4:BO','BQ4:BQ','BS4:BS','BU4:BU','BW4:BW',
  'BY4:BY','CA4:CA','CC4:CC','CE4:CE','CG4:CG','CI4:CI','CK4:CK','CM4:CM','CO4:CO','CQ4:CQ','CS4:CS',
  'CU4:CU','CW4:CW','CY4:CY','DA4:DA','DC4:DC','DE4:DE','DG4:DG','DI4:DI','DK4:DK','DM4:DM','DO4:DO',
  'DQ4:DQ','DS4:DS','DU4:DU','DW4:DW','DY4:DY','EA4:EA','EC4:EC','EE4:EE','EG4:EG','EI4:EI','EK4:EK',
  'EM4:EM','EO4:EO'];
  range1.reverse().forEach(c => sheet.getRange(c).setNumberFormat('0.00%'));
  const range = ['Q1','S1','U1','W1','Y1','AA1','AC1','AE1','AG1','AI1','AK1',
  'AM1','AO1','AQ1','AS1','AU1','AW1','AY1','BA1','BC1','BE1','BG1','BI1','BK1','BM1','BO1','BQ1',
  'BS1','BU1','BW1','BY1','CA1','CC1','CE1','CG1','CI1','CK1','CM1','CO1','CQ1','CS1','CU1','CW1',
  'CY1','DA1','DC1','DE1','DG1','DI1','DK1','DM1','DO1','DQ1','DS1','DU1','DW1','DY1','EA1','EC1',
  'EE1','EG1','EI1','EK1','EM1','EO1'];
  range.reverse().forEach(c => sheet.getRange(c).setValue('% наценки'));
 }

是否可以进一步降低请求成本并加快脚本运行速度?格式化很多文件时，它将为我节省很多时间

Is it possible to further reduce the cost of the request and speed up the script? When formatting a lot of files, it will save me a lot of time

推荐答案

我相信您的目标如下.

• 您要减少脚本的处理成本.

• 在这种情况下，我认为当使用Sheets API中的batchUpdate的RepeatCellRequest和UpdateCellsRequest时，过程成本可能会降低一些.

在使用此脚本之前，请请在Advanced Google启用Sheets API服务.并且，请设置 spreadsheetId ， sheetName 和 ranges 的变量.范围来自您的脚本.如果您想要更多范围，请将它们添加到数组中.

Before you use this script, please enable Sheets API at Advanced Google services. And, please set the variables of spreadsheetId, sheetName and ranges. ranges is from your script. When you want to more ranges, please add them to the array.

function myFunction() {
  const spreadsheetId = "###"; // Please set the Spreadsheet ID.
  const sheetName = "Sheet1"; // Please set the sheet name.
  // Please set the ranges. In this sample, a part of your ranges is used. So please modify this for your actual situation.
  const ranges = ['Q4:Q','S4:S','U4:U',,,];

  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const sheetId = sheet.getSheetId();
  const rangeList = sheet.getRangeList(ranges).getRanges();
  const requests = rangeList.flatMap(r => [
    {repeatCell:{cell:{userEnteredFormat:{numberFormat:{type:"PERCENT",pattern:"0.00%"}}},range:{sheetId:sheetId,startRowIndex:r.getRow() - 1,startColumnIndex:r.getColumn() - 1,endColumnIndex:r.getColumn() + r.getNumColumns() - 1},fields:"userEnteredFormat"}},
    {updateCells:{rows:[{values:[{userEnteredValue:{stringValue:"%наценки"}}]}],range:{sheetId:sheetId,startRowIndex:0,endRowIndex:1,startColumnIndex:r.getColumn() - 1,endColumnIndex:r.getColumn() + r.getNumColumns() - 1},fields:"userEnteredValue"}}
  ]);
  Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
}

注意:

• 在此修改后的脚本中，当电子表格具有"A"列时，当"AA"栏为"Z"时，使用时，类似的错误超出了网格限制.最大行数:1000，最大列数:26 .因此，请注意这一点.使用此脚本之前，请在工作表中为您的范围添加列.

Note:

• In this modified script, when the Spreadsheet has the columns "A" to "Z", when the column "AA" is used, an error like exceeds grid limits. Max rows: 1000, max columns: 26 occurs. So please be careful this. Before you use this script, please add columns to the sheet for your ranges.
• RepeatCellRequest
• UpdateCellsRequest

这篇关于是否可以使用setValue和setNumberFormat中的Google表格api v4进一步降低请求的成本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！