有一個我用于 google appscript 的現有代碼，它完成了前兩個步驟，即創建多個（google）作業表（從模板）并根據從另一個作業表（'main_invoice'）中提取的值重命名。但是，我仍然需要更新新作業表中的一個單元格以反映同一個表中也存在的 ID main_invoice。A:A 列是存盤 ID 的位置，并且需要在新作業表中的 B22 中輸入的值。我添加了pids獲取值的行，但需要弄清楚如何將它們插入到每個新作業表中，因為它們是從模板創建的？任何幫助，將不勝感激。謝謝！
function TemplateDuplication()
{
var ss = SpreadsheetApp.getActiveSpreadsheet(),
temp = ss.getSheetByName('blank-invoice-sample'),
all = ss.getSheetByName('main_invoice'),
sheets = ss.getSheets(),
vals = all.getRange('k:k').getValues(),
pids = all.getRange('a:a').getValues();
for (var i = 0, len = sheets.length, names = []; i < len; i )
names.push(sheets[i].getName());
for (var i = vals.length-1; i > 0; i--)
{
var name = vals[i][0];
if (name !='' && names.indexOf(name) == -1)
{
temp.activate();
ss.duplicateActiveSheet()
.setName(vals[i][0])
} }}
uj5u.com熱心網友回復：
嘗試：
function TemplateDuplication() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
const sheetNames = spreadsheet.getSheets().map(sheet => sheet.getName())
const template = spreadsheet.getSheetByName('blank-invoice-sample')
spreadsheet.getSheetByName('main_invoice')
.getDataRange()
.getValues()
.map(row => [row[0], row[10]])
.filter(row => (row[0] !== `` && row[1] !== ``) && !sheetNames.includes(row[1]))
.forEach(([id, sheetName]) => {
template.copyTo(spreadsheet)
.setName(sheetName)
.getRange(`B22`)
.setValue(id)
})
}
評論：
function TemplateDuplication() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
// Get all sheets and then 'convert' them to just the names.
const sheetNames = spreadsheet.getSheets().map(sheet => sheet.getName())
const template = spreadsheet.getSheetByName('blank-invoice-sample')
spreadsheet.getSheetByName('main_invoice')
// Get all values of `main_invoice`...
.getDataRange()
.getValues()
// And 'reduce' them to just the A and K columns.
.map(row => [row[0], row[10]])
// Remove any rows without values, and that don't have names included in `sheetNames`.
.filter(row => (row[0] !== `` && row[1] !== ``) && !sheetNames.includes(row[1]))
// For each [id, sheetName] of our filtered array...
.forEach(([id, sheetName]) => {
// Insert a copy of the template sheet...
template.copyTo(spreadsheet)
// Rename it to the sheetName...
.setName(sheetName)
// And set the cell at "B22" to the `id`.
.getRange(`B22`)
.setValue(id)
})
}
學到更多：
- 陣列.map()
- Array.filter()
- Array.forEach()
- 解構賦值
- Sheet.copyTo()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/493920.html