Блог

Я написал несколько сценариев, которые помогут мне очистить / сбросить рабочий лист, который мы используем каждую неделю. Идея в том, что у нас есть ~ 50 человек, которые используют это, и из-за сочетания злого умысла и невежества они часто находят способы обойти наши разрешения и редактировать то, что мы не хотим, чтобы они делали. В последнее время этот рабочий лист стал настолько большим, что время ожидания моих плохо написанных сценариев истекает до достижения конца. (В настоящее время у нас ~ 250 листов.) Я смог переписать большинство из них и значительно сократил время выполнения. Однако один шаг продолжает меня замедлять: удаление и повторное применение полос к определенному диапазону (A5: H27) на каждой странице.

Есть ли способ, которым я могу сделать этот код более эффективным и сократить время выполнения с ~ 12 минут для этого шага?

 function fixBanding(){
  var spreadsheet = SpreadsheetApp.getActive();
  var allSheets = spreadsheet.getSheets();
  
  allSheets.forEach(function(sheet){
    if(sheet.getSheetName() !== "HelperSheet"){
      sheet.getRange('A5:H27').activate();
      sheet.getRange("A5:H27").getBandings().forEach(banding => banding.remove());
      sheet.getRange("A5:H27").applyRowBanding()
        .setHeaderRowColor('white')
        .setFirstRowColor('#cbcbcb') //med grey
        .setSecondRowColor('white');
    }
  })
}
 

Я считаю, что ваша цель заключается в следующем.

• Ваш скрипт работает нормально. И вы хотите снизить стоимость обработки вашего скрипта.

В этом случае, как насчет использования Sheets API? Когда используется API Sheets, стоимость процесса может быть немного снижена. Когда в вашем скрипте используется API Sheets, это выглядит следующим образом.

Модифицированный скрипт:

Перед использованием этого скрипта, пожалуйста, включите Sheets API в расширенных сервисах Google.

 function myFunction() {
  // Retrieve sheet IDs and andedRange IDs.
  const ss = SpreadsheetApp.getActive();
  const spreadsheetId = ss.getId();
  const { sheetIds, bandedRangeIds } = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(properties(sheetId,title),bandedRanges(bandedRangeId))" }).sheets.reduce((o, { properties: { sheetId, title }, bandedRanges }) => {
    if (title != "HelperSheet") {
      o.sheetIds.push(sheetId);
      o.bandedRangeIds = [...o.bandedRangeIds, ...bandedRanges.map(({ bandedRangeId }) => bandedRangeId)];
    }
    return o;
  }, { sheetIds: [], bandedRangeIds: [] });
  
  // Create the request body for Sheets API.
  const firstBandColor = { red: 0.79, green: 0.79, blue: 0.79 };
  const secondBandColor = { red: 1, green: 1, blue: 1 };
  const headerColor = { red: 1, green: 1, blue: 1 };
  const requests = [...bandedRangeIds.map(id => ({ deleteBanding: { bandedRangeId: id } })), ...sheetIds.map(sheetId => ({ addBanding: { bandedRange: { rowProperties: { headerColor, firstBandColor, secondBandColor }, range: { sheetId, startRowIndex: 4, endRowIndex: 27, startColumnIndex: 0, endColumnIndex: 8 } } } }))
  ];
  
  // Request to Sheets API using the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);

  // If you want to activate 'A5:H27' in each sheet, please use the following script.
  // SpreadsheetApp.flush();
  // ss.getSheets().forEach(sheet => {
  //   if (sheet.getSheetName() !== "HelperSheet") sheet.getRange('A5:H27').activate();
  // });
}
 

Ссылки:

• Метод: spreadsheets.batchUpdate
• DeleteBandingRequest
• AddBandingRequest

Похоже, ваш код достаточно оптимизирован, я думаю, вы можете разделить запуск на несколько запусков:
1) В начале цикла инициализируйте переменную date для хранения текущего времени
2) при циклическом просмотре листов продолжайте проверять общее время, прошедшее с момента запуска
3) если общее времяболее 80% от общего доступного времени выполнения скрипта (6 минут), затем запустите новое расписание запуска после, скажем, 20-секундного устанавливаемого триггера
4) а также сохранить номер или имя листа, до которого вы уже выполнили обработку, в новом листе Excel (скажем, config)
5) при запуске триггера код должен принимать имя / номер листа с листа «config», который является листом, до которого вы уже выполнили обработку.обработка уже выполнена, и начните обработку после этого номера листа.