我正在使用下面的代碼在 google sheet（由 Cooper 提供）中創建驗證，該腳本的作用是自動檢查適用的標題并使用值創建下拉串列并隱藏不適用的列。
我想在這里解決的是：
- 該腳本檢查與產品選擇相關的適用標題
- 它使用驗證值創建下拉串列
- 它不是隱藏不適用的列，而是將它們從作業表中洗掉
我是谷歌腳本的初學者并嘗試使用 deletecolumn 函式但無法使其作業。
請幫幫我。
function loadObjectsAndCreateProductDropDown() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const psh = ss.getSheetByName('Sheet1');
const [ph, ...prds] = sh.getRange(1, 1, 10, 6).getValues().filter(r => r[0]);
const [ch, ...chcs] = sh.getRange(11, 1, 10, 10).getValues().filter(r => r.join());
let pidx = {};
ph.forEach((h, i) => { pidx[h] = i });
let prd = { pA: [] };
prds.forEach(r => {
if (!prd.hasOwnProperty(r[0])) {
prd[r[0]] = { type: r[pidx['Type']], size: r[pidx['Size']], color: r[pidx['Color']], material: r[pidx['Material']], length: r[pidx['Length']] };
prd.pA.push(r[0]);
}
});
let cidx = {};
let chc = {};
ch.forEach((h, i) => { cidx[h] = i; chc[h] = [] });
chcs.forEach(r => {
r.forEach((c, i) => {
if (c && c.length > 0) chc[ch[i]].push(c)
})
})
const ps = PropertiesService.getScriptProperties();
ps.setProperty('product_matrix', JSON.stringify(prd));
ps.setProperty('product_choices', JSON.stringify(chc));
Logger.log(ps.getProperty('product_matrix'));
Logger.log(ps.getProperty('product_choices'));
psh.getRange('A2').setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(prd.pA).build());
}
//I chose to use an installable dropdown. I'm not sure if it's needed. Its up to you.
function onMyEdit(e) {
//e.source.toast('entry')
const sh = e.range.getSheet();
if (sh.getName() == 'Sheet1' && e.range.columnStart == 1 && e.range.rowStart == 2 && e.value) {
//e.source.toast('flag1');
sh.getRange('C2:G2').clearDataValidations();
let ps = PropertiesService.getScriptProperties();
let prodObj = JSON.parse(ps.getProperty('product_matrix'));//recovering objects from PropertiesService
let choiObj = JSON.parse(ps.getProperty('product_choices'));
let hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues().flat();
let col = {};
hA.forEach((h, i) => { col[h.toLowerCase()] = i 1 });
["type", "size", "color", "material", "length"].forEach(c => {
if (choiObj[prodObj[e.value][c]]) {
sh.getRange(e.range.rowStart, col[c]).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(choiObj[prodObj[e.value][c]]).build()).offset(-1,0).setFontColor('#000000');
sh.showColumns(col[c])
} else {
sh.getRange(e.range.rowStart, col[c]).offset(-1,0).setFontColor('#ffffff');
sh.hideColumns(col[c]);
}
})
}
}
演示表
Data2 作業表標簽：
更新的 示例腳本
var sh = SpreadsheetApp.getActiveSpreadsheet();
var selection = sh.getSheetByName("Sheet1").getRange("A2").getValue(); //Get the selection on the dropdowm on cell A2
var data1 = sh.getSheetByName("Data1").getDataRange().getDisplayValues();
var data2 = sh.getSheetByName("Data2").getDataRange().getDisplayValues();
function addHeaders(sheet, values) { //Adds the headers from Column C and beyond
var startCol = 3; //Column C
var endCol = startCol values.length;
values.forEach(x => {
if(startCol <= endCol){
if(checkHeaderIfYesOrNo(x) == true)return;
sheet.getRange(1,startCol).setValue(x);
startCol = 1;
}
});
}
function onEdit(e) {
if(e.range.getA1Notation() != "A2")return;//Make sure to run onEdit function ONLY when cell A2 is edited/selected
var headers = [];
var headerValues = [];
var temp = [];
var counter = 0;
clean();
data1 = fixDuplicates();
//find selection name on data1
for(var x = 0; x< data1.length; x ){
var name = data1[x][0];
if(name == selection){
///get the headers & their values
data1[x].forEach(res => {
if(res != "" & res != selection){
var index1 = data1[x].indexOf(res);
var index2 = data2[0].indexOf(res);
headers.push([data1[0][index1]]);
for(var y=0; y< data2.length; y ){
if(data2[y][index2] != "" && data2[y][index2] != res){
temp.push("**" res "**" data2[y][index2]); //place raw header data to a temporary variable
}
}
//Set the drop-down data of each headers
temp.forEach(raw => { //clean the temp array
if(raw.includes(res)){
var regex = /\*\*([^*]*(?:\*(?!\*)[^*]*)*)\*\*/g;
headerValues.push(raw.replace(regex, ""))
}
});
//Logger.log("Data of the \"" res "\" header:\n" headerValues);
//set data validation per header
counter = 1;
if(res.toLowerCase().includes("no"))return; //skip creating data validation for "No" header
if(res.toLowerCase().includes("yes")) return; //skip creating data validation for "Yes" header
sh.getSheetByName("Sheet1").getRange(2,2 counter).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(headerValues).build());
headerValues = [];
}
});
}
}
addHeaders(sh.getSheetByName("Sheet1"), headers);
}
function clean(){ //Clean Sheet 1 on every edit
sh.getSheetByName("Sheet1").getRange('C2:Z').clearDataValidations();
sh.getSheetByName("Sheet1").getRange('C1:Z').clearContent();
}
function fixDuplicates(){
var temp = [];
var data1New = [];
var count = 1;
for(var x=0; x<data1.length; x ){
data1[x].forEach(findIt => {
if(findIt.toLowerCase().includes("yes") || findIt.toLowerCase().includes("no")){
temp.push(findIt count);
count = 1;
}else{
temp.push(findIt);
}
})
data1New.push(temp);
temp=[];
}
return data1New;
}
function checkHeaderIfYesOrNo(h){
for(var x=0; x<data1.length; x ){
if(data1[x][0] == selection){
if(data1[x][data1[0].indexOf(h.toString())].toLowerCase().includes("yes")){
Logger.log(h " contains Yes");
return null;
}else if(data1[x][data1[0].indexOf(h.toString())].toLowerCase().includes("no")){
Logger.log(h " header will not be added as it has \"No\" value");
return true;
}else{
Logger.log("Skip the " h " header");
return null;
}
}
}
}
示例演示：
示例執行日志結果供審查：
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/419484.html
標籤：