在這里發布問題的新手和使用 Google 表格的 Apps 腳本的新手。
我正在嘗試讓宏根據以“~”開頭的模板表創建一個新表。宏應該在另一張作業表的 A 列中找到以“Y”開頭的任何內容。如果它有一個“Y”，它應該獲取同一行上的供應商名稱值，但列 B。然后它用于創建新作業表并為其命名。
下面的代碼執行，但沒有任何反應。我能夠復制模板表，但在供應商表選項卡中添加搜索似乎導致了問題。任何幫助表示贊賞。我也提前道歉，第一次海報所以不完全確定這一切都有意義。
function DuplicateSht() {
var Sect = 0
var Vend = 0
var sh = SpreadsheetApp.getActiveSpreadsheet()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var data = sh.getDataRange().getValues(); //
for (var i=0 ; i<sheets.length ; i ) {
SpreadsheetApp.setActiveSheet(sheets[i])
if (sheets[i].getName().startsWith("Vendor Sheet")){//locates sheet with base information
Sect = sh.getRange("C2").getValue();//pulls in section number
Vend = sh.getRange("C3").getValue();//pulls in number of vendors
for (var j=10 ; j<Vend 10; j ){
if(data[j][1] == "Y"){//Looks for each vendor with a "Y" in column A. The number of vendors to review is variable
var VendName = sh.getRange("B" j).getValue();//If a vendor has a "Y" will grab their name
for (var k=0 ; k<sheets.length ; k ) {
SpreadsheetApp.setActiveSheet(sheets[k])
for (var l=0 ; l<Vend ; l ){
if (sheets[l].getName().startsWith("~")){
sh.duplicateActiveSheet()//duplicates the sheet beginning with "~"
sh.renameActiveSheet("x-" VendName " " Sect)//renames the sheet based on the data from the prep template sheet, including vendor name
}
}
}
}
}
}
}
}
uj5u.com熱心網友回復：
我對您的挑戰的理解是，您有一個包含各種供應商資訊的電子表格。供應商都列在一張紙上。然后每個供應商，當他們在該串列表的一列中獲得“Y”時，會獲得一個基于模板創建的專用表。
如果這種結構是正確的，那么您可以避免原始代碼中的嵌套陳述句，并且更直接地查找供應商和創建作業表。
這是我創建的電子表格，它演示了這一點并顯示了以下代碼的實際操作。
function DuplicateSht() {
//since there is only one Vendor Sheet we can just set a variable pointing to it, then get the data in the sheet using getDataRange
var ss = SpreadsheetApp.getActiveSpreadsheet()
var ws = ss.getSheetByName("Vendor Sheet")
var data = ws.getDataRange().getValues()
//this "reduce function" filters the data so we have an array ("vendors") that only includes those rows with a "Y" in the first column
var vendors = data.reduce(function(acc, curr) {
if(curr[0] === "Y") {
acc.push(curr)
}
return acc
},[])
//now loop through the vendors list and for each one set up the desired sheet name -- before duplicating the template, make sure it doesn't already exist.
for (i=0; i<vendors.length; i ){
var sect = vendors[i][2]
var name = "x-" vendors[i][1] " " sect
//get the names of all the sheets
var sheets = ss.getSheets().map(function (r){return r.getName()})
//check to see if the current vendor you are evaluating has already had a sheet created for it. If not, then create a new sheet based on the template
if (sheets.indexOf(name) === -1){
ss.setActiveSheet(ss.getSheetByName("~Template"))
ss.duplicateActiveSheet()
ss.renameActiveSheet(name)
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/344419.html