google-apps-scriptgoogle-sheetsgoogle-sheets-apibatch-request

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

Solution

  • I believe your goal as follows.

    Modification points:

    Modified script:

    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);
}

    Note:

    References: