根据列值将一张谷歌工作表拆分为多张工作表-替换重复的工作表

Question

这已经超出了我的知识水平，我希望得到帮助。下面的脚本有一些限制。此脚本检查是否存在区域选项卡，如果不存在，则源工作表中的区域数据将按该区域的名称复制到新选项卡。区域是源工作表的第24列，数据从第3行开始，标题是第2行。

如果区域选项卡已经存在，我希望它被删除，重新创建或用当前数据重新填充，而不是跳过。

function createSheets(){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const sourceWS = ss.getSheetByName("Forecast (SQL) Validation")
const regions = sourceWS
.getRange(3,24,sourceWS.getLastRow()-2,1)
.getValues()
.map(rng => rng[0])
const uniqueRegion = [ ...new Set(regions) ]
const currentSheetNames = ss.getSheets().map(s => s.getName())
let ws
uniqueRegion.forEach(region => {
if(!currentSheetNames.includes(region)){
ws = null
ws = ss.insertSheet()
ws.setName(region)
ws.getRange("A2").setFormula(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`)
sourceWS.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"))
}//If regions doesn't exist
})//forEach loop through the list of region
} //close createsheets functions

Answer 1

这样试试

function createSheets() {
const ss = SpreadsheetApp.getActive()
const ssh = ss.getSheetByName("Forecast (SQL) Validation");
const regions = ssh.getRange(3, 24, ssh.getLastRow() - 2, 1).getValues().flat();
const urA = [...new Set(regions)];
const shnames = ss.getSheets().map(s => s.getName())
let ws;
urA.forEach(region => {
let idx = shnames.indexOf(region);
if (~idx) {
ss.deleteSheet(ss.getSheetByName(shnames(idx)));//if it does exist delete it and create a new one
}//if it does not exist the just create a new one
ws = null;
ws = ss.insertSheet(region);
ws.getRange("A2").setFormula(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`)
ssh.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"))
})
}

说明：

循环浏览所有选项卡，如果选项卡已经存在，请在再次创建之前通过deleteSheet将其删除，就像处理不存在的选项卡一样。。