如何在 google 脚本中使用"OpenbyID"对多个电子表格执行操作

Question

请耐心等待，因为我是一个新手，但我已经在一个文件夹中形成了一个电子表格ID的列表，我已经将其存储在一列的表格中。我正在尝试执行一个脚本，该脚本将进入每个电子表格，并删除给定名称的表格（如果它们在其中）。到目前为止，这就是我所拥有的，但我不确定我在哪里搞砸了：

function cleanAllOld () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('fileList');
  var numberFiles = sheet.getLastRow();
  for (var i=0; i<numberFiles; i++) {
    try{
// File to be cleaned
  var fileID = sheet.getRange(2+i, 1, numberFiles-1).getValues();

  var destination = SpreadsheetApp.openById(fileID[i]);
  var sheet1 = destination.getSheetByName('Copy of CALC');
  var sheet2 = destination.getSheetByName('Copy of Print');
  var sheet3 = destination.getSheetByName('Copy of Markbook');
 if (destination.sheet1 == null) {
  } else {
    destination.deleteSheet(sheet1);
  }
  if (destination.sheet2 == null) {
  } else {
    destination.deleteSheet(sheet2);
  }
  if (destination.sheet3 == null) {
  } else {
    destination.deleteSheet(sheet3);
  }
}
    catch (e) {
      Logger.log(e);
    }
}
}

固定脚本

     function cleanAllOld () {
  var ss = SpreadsheetApp.openById('...');
  var sheet = ss.getSheetByName('fileList');
  var numberFiles = sheet.getLastRow();
  for (var i=0; i<numberFiles; i++) {
// File to be cleaned
  var fileID = sheet.getRange(1, 1, numberFiles).getValues();
 Logger.log('fileID[i][0]: ' + fileID[i][0]);       
  var destination = SpreadsheetApp.openById(fileID[i][0]);
  var sheet1 = destination.getSheetByName('Copy of CALC');
  var sheet2 = destination.getSheetByName('Copy of Print');
  var sheet3 = destination.getSheetByName('Copy of Markbook');
 if (sheet1 === null) {
  } else {
    destination.deleteSheet(sheet1);
  }
  if (sheet2 === null) {
  } else {
    destination.deleteSheet(sheet2);
  }
  if (sheet3 === null) {
  } else {
    destination.deleteSheet(sheet3);
  } 
}
}

Answer 1

getValues()方法返回一个二维数组。您收到的错误消息来自：

var destination = SpreadsheetApp.openById(fileID[i])

尝试使用：

var destination = SpreadsheetApp.openById(fileID[i][0])

添加第二个带零的索引。

Answer 2

解决了问题，我在下面用粗体显示了更正的行

  function cleanAllOld () {
  var ss = SpreadsheetApp.openById('...');
  var sheet = ss.getSheetByName('fileList');
  var numberFiles = sheet.getLastRow();
  for (var i=0; i<numberFiles; i++) {
// File to be cleaned
  **var fileID = sheet.getRange(1, 1, numberFiles).getValues();**
 Logger.log('fileID[i][0]: ' + fileID[i][0]);       
  var destination = SpreadsheetApp.openById(fileID[i][0]);
  var sheet1 = destination.getSheetByName('Copy of CALC');
  var sheet2 = destination.getSheetByName('Copy of Print');
  var sheet3 = destination.getSheetByName('Copy of Markbook');
 if (sheet1 === null) {
  } else {
    destination.deleteSheet(sheet1);
  }
  if (sheet2 === null) {
  } else {
    destination.deleteSheet(sheet2);
  }
  if (sheet3 === null) {
  } else {
    destination.deleteSheet(sheet3);
  } 
}
}