如何使用Google App脚本从工作表名称创建字符串? [英] How to create a string from the sheet names using Google App Scripts?
问题描述
我正在尝试通过添加所有工作表名称(名称中同时包含字母和数字)来创建字符串.我需要生成的确切字符串从= QUERY到0)开始,其中PHY4101,MATH4103,HUM4105,ICTE4115等来自电子表格的工作表名称.最后,该字符串将被设置为Master
表的A2单元格的值.如何解决?
I am trying to create a string by adding all sheet names (having both alphabets and numbers in their names). The exact string I need to generate is starting from =QUERY to 0) where PHY4101, MATH4103, HUM4105, ICTE4115 etc. comes from the sheet names of a spreadsheet. Finally the string will be set as a value to A2 cell of Master
sheet. How to fix it?
要产生的确切字符串: = QUERY({PHY4101!A2:F; MATH4103!A2:F; HUM4105!A2:F; ICTE4115!A2:F; ICTE4117!A2:F; ICTE4143!A2:F; PHY4102!A2:F; HUM4106!A2: F; ICTE4144!A2:F},选择*,其中Col3不为空" ,, 0)
The exact string to produce: =QUERY({PHY4101!A2:F;MATH4103!A2:F;HUM4105!A2:F;ICTE4115!A2:F;ICTE4117!A2:F;ICTE4143!A2:F;PHY4102!A2:F;HUM4106!A2:F;ICTE4144!A2:F},"select * where Col3 is not null",0)
function formatSheetsP3() {
var sss = SpreadsheetApp.getActiveSpreadsheet();
const allsheets = sss.getSheets().filter(sh => /\d/.test(sh.getName()));
for(var s in allsheets){
var sheet = allsheets[s].getName();
var str = '=QUERY({'+ sheet + '!A2:F;'
}
str2 = str + '},"select * where Col3 is not null",0)';
var master = sss.getSheetByName("Master");
master.getRange("A2").setValue(str2);
}
推荐答案
您可以进一步简化代码.
You can simplify your code even more.
这是解决方案:
function formatSheetsP3() {
let sss = SpreadsheetApp.getActiveSpreadsheet();
let allsheets = sss.getSheets().filter(sh => /\d/.test(sh.getName())).map(n=>n.getName());
let mid ='';
allsheets.forEach((sh,index)=>{
let temp = index>0 ? `;${sh}!A2:F`:`${sh}!A2:F`;
mid+=temp;
})
let str2 = `=QUERY({${mid}},"select * where Col3 is not null",0)`
let master = sss.getSheetByName("Master");
master.getRange("A2").setValue(str2);
}
这篇关于如何使用Google App脚本从工作表名称创建字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!