如何在多张纸上运行Google脚本? [英] How do I run Google script on multiple sheets?

问题描述

我已经看到这个问题在这里返回了很多结果，但是我很难使其适用于我的用例.基本上，我有一个简单的脚本，希望在电子表格中的多个工作表上运行(不是每个工作表，而是我定义的工作表).我尝试定义工作表并运行for循环，但是在调用方法getRange时失败.无论如何，这是我最初的脚本，仅定义一张纸，并且按预期工作:

I've seen this question return quite a few results here, but I'm having trouble getting it to work for my use case. Basically, I have a simple script that I want to run on multiple sheets in my spreadsheet (not every sheet, just the ones I define). I've tried defining the sheets and running a for loop, but it fails when calling the method getRange. Anyway, here's my original script that defines only one sheet, and this works as expected:

var sheetToSort = "Sheet1";
var columnToSortBy = 1;
var rangeToSort = "A2:AB";

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  if (editedCell.getColumn() == columnToSortBy && sheet.getName() == sheetToSort) {
    sortFormResponsesSheet();
  }
}

function sortFormResponsesSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetToSort);
  var range = sheet.getRange(rangeToSort);
  range.sort( { column : columnToSortBy, ascending: false } );
}

推荐答案

这是将设置放入对象然后在对象中循环的示例.

This is an example of putting settings into an object and then looping through the object.

function onEdit() {
  const sheet = SpreadsheetApp.getActiveSheet(),
        editedCell = sheet.getActiveCell(),
        editedColumn = editedCell.getColumn(),
        sheetName = sheet.getName();

  // Define all the sheets to sort using an object literal
  const objSheetsToSort = {
    "Sheet1": {columnToSortBy: 1, rangeToSort: "A2:AB"},
    "Sheet2": {columnToSortBy: 2, rangeToSort: "A2:AB"},
    "Sheet3": {columnToSortBy: 3, rangeToSort: "A2:AB"},
    "Sheet4": {columnToSortBy: 4, rangeToSort: "A2:AB"}
  }

  // Get the sort info for this sheet (or `undefined` if we didn't define how to sort the edited sheet).
  var config = objSheetsToSort[sheetName];
  if (config && editedColumn === config.columnToSortBy) {
    // We want to sort the edited sheet.
    sortSheet_(sheet, config);
  }
}

// This function can only be called by other functions in this Script Project, not manually or via menu.
function sortSheet_(sheetToSort, objectOfSettings) {
  Logger.log('sheetToSort: ' + sheetToSort.getName())

  var range = sheetToSort.getRange(objectOfSettings.rangeToSort);
  range.sort( { column : objectOfSettings.columnToSortBy, ascending: false } );
}

这篇关于如何在多张纸上运行Google脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！