通过获取单元格范围来获取带有 URL 的工作表名称
2022-01-19
1861
如何通过获取电子表格中包含 URL 的单元格范围来获取具有公式/自定义函数的工作表名称?
我正在使用以下代码,但出现以下错误
TypeError: Cannot read property 'length' of undefined
function getFileNames() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Links");
var links = sheet.getRange("B3:B").getValues();
var filenames = [];
for (var i = 0; i < links.length; i++) {
var url = links[i][0];
if (url != "") {
var filename = SpreadsheetApp.openByUrl(links[i][0]).getName();
filenames.push([filename]);
}
}
var startRow = 3; // print in row 2 since row 1 is the header row
var fileNameColumn = 3; // Column B = column 2
var destination = sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length);
destination.setValues(filenames);
}
2个回答
长度未定义表示您的链接未定义。查看以下脚本以获取示例工作代码:
脚本:
function getFileNames() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Links");
var links = sheet.getRange("B3:B").getValues();
// 1st: get a 1D array representation of the links (flat)
// 2nd: remove the empty rows (filter)
// 3rd: return the spreadsheet names of the remaining rows to filenames
var filenames = links.flat().filter(String).map(link => {
return [SpreadsheetApp.openByUrl(link).getName()];
});
var startRow = 3; // data will start at 3rd row
var fileNameColumn = 3; // data will be written at 3rd column which is column C
sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length)
.setValues(filenames);
}
输出:
NightEye
2022-01-19