在Google Apps脚本中创建所有工作表名称的列表 [英] Create list of all sheet names in Google Apps Scripts

问题描述

我有两个电子表格SpreadSheetA和SpreadSheetB.

I have two spreadsheets, SpreadSheetA and SpreadSheetB.

我正在尝试获取SpreadSheetA中所有工作表名称的列表，并将其添加到SpreadSheetB中的活动工作表中.

I am trying to get a list of the names of all the sheets in SpreadSheetA and add it to the active sheet in SpreadSheetB.

我已经完成了代码，但是在运行它时不断收到错误消息.

I have the code done but keep getting an error message when I run it.

相对于行range.setValues(sheetNameArray);，错误消息为无法将数组转换为对象[] []".

The error message is "Cannot convert Array to Object[][]" in reference to the line range.setValues(sheetNameArray);.

这是我的代码:

function getSheetNamesAndAddToOtherSheet() {
  var spreadSheetAURL = "https://docs.google.com/spreadsheets/d/UNIQUE-ID-HERE/edit"; 
  var sheetNameArray = [];

  var spreadSheetsInA = SpreadsheetApp.openByUrl(spreadSheetAURL).getSheets();

  for (var i = 0; i < spreadSheetsInA.length; i++) {
    sheetNameArray.push(spreadSheetsInA[i].getName());
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(1, 1, sheetNameArray.length, 1);
  range.setValues(sheetNameArray);
}

推荐答案

错误消息提示出了什么问题:setValues方法要求使用 double 数组的"Object [] []"东西的.在Google表格中，所有数据范围均表示为双精度数组，例如[[1, 2], [3, 4]].如果有一行，它仍然是双精度数组:[[1, 2]].如果是一列:[[1], [3]].

The error message hints at what's wrong: the setValues method expects "Object[][]" that is a double array of things. In Google Sheets, all ranges of data are represented as double arrays, such as [[1, 2], [3, 4]]. If there is one row, it's still a double array: [[1, 2]]. If it's one column: [[1], [3]].

因此，您需要这个额外的数组维，实际上是将每个工作表名称都包装在方括号中.这是在下面完成的，在这里我还使用了 map 方法来处理工作表数组:与在for循环中使用push相比，此方法更清洁，而且效率更高.

So you need this extra array dimension, essentially wrapping every sheet name in square brackets. This is done below, where I also used map method to process the array of sheets: this is cleaner and likely more efficient than using push in a for loop.

function getSheetNamesAndAddToOtherSheet() {
  var spreadSheetAURL = "https://docs.google.com/spreadsheets/d/UNIQUE-ID-HERE/edit"; 
  var sheetNameArray = [];

  var spreadSheetsInA = SpreadsheetApp.openByUrl(spreadSheetAURL).getSheets();

  sheetNameArray = spreadSheetsInA.map(function(sheet) {
    return [sheet.getName()];
  });

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(1, 1, sheetNameArray.length, 1);
  range.setValues(sheetNameArray);
}

这篇关于在Google Apps脚本中创建所有工作表名称的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！