我有一張名為“Main_Sheet”的作業表。Col_B 中的每個列值都有多個選項卡（東、中）。每周都會將新記錄添加到主作業表中。我想要一個谷歌應用程式腳本函式將新行從 Main_Sheet 選項卡插入到相應的選項卡。
輸入表：
Main_Sheet
Output_Sheets 東中部
要遵循的步驟：概述的行是新的（因為它們尚不存在于東部和中央表中）。獲取這些行并將它們的值粘貼到相應的選項卡中（東部和中部）
輸出表應如下所示：
運行腳本后，應將突出顯示的行添加到“東”選項卡：
同樣，Central 看起來像：
由于“West”選項卡不存在，我希望腳本為其創建一個新選項卡并插入記錄
我是谷歌表格和應用程式腳本的新手，請幫忙
uj5u.com熱心網友回復：
在您的情況下，以下示例腳本怎么樣？
示例腳本：
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets().reduce((o, s) => (o[s.getSheetName()] = s, o), {});
const main = ss.getSheetByName("Main_Sheet");
const [header, ...values] = main.getDataRange().getValues();
const col = main.getLastColumn();
const obj = values.reduce((o, r) => (o[r[1]] = o[r[1]] ? [...o[r[1]], r] : [r], o), {});
Object.entries(obj).forEach(([s, v]) => {
if (v.length == 0) return;
if (sheets[s]) {
const sheet = sheets[s];
const temp = sheet.getDataRange().getValues().reduce((o, r) => (o[r.join("")] = true, o), {});
const values = v.filter(r => !temp[r.join("")]);
if (values.length > 0) {
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow 1, 1, values.length, values[0].length).setValues(values);
main.getRange("A2:2").copyFormatToRange(sheet, 1, col, lastRow 1, lastRow values.length);
}
} else {
const values = [header, ...v];
const sheet = ss.insertSheet(s);
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
main.getRange(1, 1, 1, col).copyFormatToRange(sheet, 1, col, 1, 1);
main.getRange("A2:2").copyFormatToRange(sheet, 1, col, 2, values.length);
}
});
}
在此示例腳本中，值是從“Main_Sheet”中檢索的。并且，檢索每個作業表的每個值，并將每個值放入每個作業表。
關于您的追加要求
Can you please include headers as well. Right now the headers are missing. Once you add headers I will test again，我反映了。關于您的附加要求
the header row is in bold values in the "Main_Sheet" tab. With your code, the headers are just being copied as plain text. I want the format of header to be same in all tabs，我反映了。關于您的追加要求
I am saying that I want to keep the format from the main_sheet tab consistent with other tabs. So if Main_sheet has blue and bold headers, the code should copy the header value and format into the new tab.，我反映了。關于您的追加要求
The goal is the paste the values from Main_Sheet to the correct tabs. The format(of header and all other rows) in main_sheet should be same as Central, East, West Tab. Right now your code is pasting plain values(it is not pasting the correct format from the Source sheet--> Main_Sheet)... Right now only header row has the correct format in the output tabs. Please preserve the format of the non header rows as well，我反映了。
筆記：
- This sample script is for your showing sample Spreadsheet. So, when your Spreadsheet is changed, this script might not be able to be used. Please be careful about this.
References:
- reduce()
- filter()
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/442363.html