For context, I have a google sheet that we use for inventory and pricing. I have made another spreadsheet that imports the ranges that we need from the original to show wholesale inventory to customers without any pricing. I am currently using ImportRange()
to copy the content, but I would like to maintain the formatting of the previous sheet.
I am using this onEdit()
script to update the formatting (I am using a trigger in the original):
function setFormatOnEdit(e) {
if (!e)
throw new Error("This function is automatically called, do not run this manually")
const lock = LockService.getScriptLock()
if (lock.tryLock(350000)) {
try {
const {
master,
sources
} = variables_();
const { range, source } = e;
const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
const srcRange = source.getRange(editRange);
const editedSheetName = range.getSheet().getSheetName();
if (editedSheetName != srcRange.getSheet().getSheetName())
return;
//Reformatting Code Here
} catch ({stack}) {
console.error(stack)
} finally {
lock.releaseLock();
console.log("Done");
}
} else { console.error("Timeout") }
}
And variables_() is:
function variables_() {
const masterSpreadsheetId = "###"
const sourceSheetName1 = "Mixed Oak"
...
const sourceSheetName13 = "Misc/Random Inventory"
const sourceSheet1Range = "Mixed Oak!A1:I"
...
const sourceSheet13Range = "Misc/Random Inventory!A1:I"
return {
master: { masterSpreadsheetId },
sources: [
{ sheetName: sourceSheetName1, editRange: sourceSheet1Range },
...
{ sheetName: sourceSheetName13, editRange: sourceSheet13Range }
]
};
}
Currently I am having the issue of e.source returning the first sheet in the source spreadsheet (which is a summary of pricing sheet; I do not want to copy this sheet over) after every edit, regardless of which sheet is edited.
This poses a problem because I am trying to use the sheet name of the edited original to reformat the copy sheet on each edit.
I could just use conditional formatting to reformat the copy, but there are less options possible and I would need to do a search through all rows for certain formatting parameters, which would take forever to update and is just clunky.
Let me know if there is a way to fix this issue, or if there is a better way to do what I am trying to do with copying over.
The source
property of the on-edit event trigger returns the active SpreasheetApp.Spreadsheet
, not a SpreadsheetApp.Sheet
.
On the other hand, the following part of the script in the question doesn't make sense to me:
const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
const srcRange = source.getRange(editRange);
const editedSheetName = range.getSheet().getSheetName();
if (editedSheetName != srcRange.getSheet().getSheetName())
return;
If you are looking to get the edited range use e.range
, e.source.getActiveSheet().getActiveRange()
or SpreadsheetApp.getActiveSheet().getActiveRange()
. The first option is preferred for brevity, and intuitively, it should be faster. Also, the object returned is extended with the properties rowStart
, columnStart
, rowEnd
and columnEnd
, which are handy in many use cases.
To get the name of the edited sheet you might use e.range.getSheet().getName()
or e.source.getActiveSheet().getName()
, SpreadsheetApp.getActiveSheet().getName()
. Any of the first two options are preferred.
Note: Regarding explaining spreadsheet concepts and how Google spreadsheet works, I think that the Google Sheets API documentation is more complete and clear than the documentation of the Google Apps Script Spreadsheet Service and even the Google Sheets section on https://support.google.com/docs. I mentioned this because the Google Sheets API documentation mentions how is determined the sheet or range to be returned on several cases.