I'm working on an interlinked raw materials inventory spreadsheet and a brew log, and I want to be able to pull the current inventory into the brew log sheet on a click.
I've got the importRawMat function connected to the button "Boop", which works just fine to import the data I need, but I don't have any success linking it to a checkbox click with onEdit, despite onEdit triggering the 'Clear' function just fine.
I'm wondering if it's related to Simple Triggers being restricted around services that require authorisation? If so, how can I get a trigger on this function that will be accessible through the Google Sheets App, as buttons and custom menus don't work through there.
See below for link and code:
function onEdit(e) {
if (!e.value) return
if (e.value !== "TRUE") return
if (e.range.rowStart !== 1) return
if (e.range.columnStart !== 1) return
if (e.range.getSheet().getName() !== "Brew") return
importRawMat()
}
function onEdit(e) {
if (!e.value) return
if (e.value !== "TRUE") return
if (e.range.rowStart !== 2) return
if (e.range.columnStart !== 3) return
if (e.range.getSheet().getName() !== "Brew") return
Clear()
e.range.uncheck()
}
function Clear() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRangeList(['A1', 'A1']).activate()
.uncheck();
};
function importRawMat() {
var originsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1GOmX04m8OC1dFWFdhBonLTt07SImkq6gGx5l9l6SSXA/edit?gid=411516577#gid=411516577').getSheetByName('Malt');
var origindata = originsheet.getDataRange().getValues();
var destinationsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Blank');
destinationsheet.clearContents();destinationsheet.getRange(1,1,origindata.length,origindata[0].length).setValues(origindata);
}
onEdit
cannot be used for SpreadsheetApp.openByUrl
. I'm not sure whether you are using the installable OnEdit trigger from your question. So, I guessed that this might be one of several modification points.onEdit
. In this case, the script cannot be correctly worked.When these points are reflected in your showing script, it becomes as follows.
Please do the following flow.
onEdit
functions from your project.installedOnEdit
. RefBy this flow, when you check the checkbox of "A1" or "C2" of the Brew sheet, the function installedOnEdit
is automatically run by the installable OnEdit trigger.
function installedOnEdit(e) {
const { range } = e;
const sheet = range.getSheet();
if (range.isBlank() || !range.isChecked() || sheet.getName() != "Brew") return;
if (range.rowStart == 1 && range.columnStart == 1) { // or if (range.getA1Notation() == "A1") {
importRawMat();
} else if (range.rowStart == 2 && range.columnStart == 3) { // or else if (range.getA1Notation() == "C2") {
Clear();
range.uncheck();
}
}
onEdit
function of your showing script. Also, this script supposes that your functions Clear()
and importRawMat()
are working fine. Please be careful about this.