0
148
VisualizaçõesUpdate multiple Tabs using apps script
I have a sheet called "Main_Sheet". I have multiple tabs(East,Central) for each column value in Col_B. Every week new records are added to the main sheet. I want a google apps script function to insert new rows from Main_Sheet tab to the corresponding tabs.
Input sheet:
Main_Sheet
Output_Sheets East Central
Steps to be followed: The outlined rows are new(because they do not exist in the East and Central Sheets as yet). Take these rows and paste their values in the appropriate tabs(East and Central)
Output Sheet should look as follows:
After running the script the highlighted rows should be added to "East" tab:
Similarly, Central would look like:
Since "West" tab doesn't exist, I would like the script to create a new tab for it and insert records
I am new to google sheets and apps script, please help
1 Respostas
Responde à pergunta0
In your situation, how about the following sample script?
Sample script:
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);
}
});
}
In this sample script, the values are retrieved from "Main_Sheet". And, retrieve each value for each sheet, and each value is put to each sheet.
About your additional request of
Can you please include headers as well. Right now the headers are missing. Once you add headers I will test again, I reflected it.About your additioanl request of
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 reflected it.About your additional request of
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., I reflected it.About your additional request of
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, I reflected it.
Note:
- 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.