根据您的情况,以下示例脚本怎么样?
示例脚本:
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
,我反映了。
Note:
- 此示例脚本用于显示示例电子表格。因此,当您的电子表格发生更改时,此脚本可能无法使用。请注意这一点。
参考:
- reduce() https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reduce
- filter() https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter