My question is, how can I get Macro1 to work as intended (complete the network request(s) and open the model popup with embedded hyperlink) when called from the checkbox onEdit listener?
I've written a simple Script (Macro1) in Google Sheets which performs a network request then opens a model popup with a hyperlink. This works on desktop when activated via textbox click action (Macro1 runs and network request is complete and modal popup is presented with working hyperlink). However, this button does not work in mobile. So the workaround is to use a checkbox because mobile can handle the onEdit() signal from a checkbox.
OnEdit does handle the checkbox edit action and calls Macro1 which does not complete fully. Macro1 completes up until the network request supposed to be sent but no response is received and no modal popup. I dump the network response in cell A1 for visual cue when run from the spreadsheet.
Here is relevant code...
onEdit(e)
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
Logger.log("edit active on range " + r.getA1Notation())
if (r.getRow()== 4 && r.getColumn()== 2 && ss.getName()=='Sheet1') {
Logger.log("should send macro 1")
Macro1()
return
}
Macro1()
//ASSIGN COACH ID STRING
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B2').activate();
var range = spreadsheet.getActiveRange();
var coach = range.getValues().toString();
Logger.log(coach)
//SELECT THE DATA
spreadsheet.getRange('A8').activate();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
range = spreadsheet.getActiveRange();
values = range.getValues();
var url = "https://www.MYWEBPAGE.com/SOMEPHPPAGE.php?coach=" + coach;
var options = {
method: "post",
};
var response = UrlFetchApp.fetch(url, options);
Logger.log(response)
spreadsheet.getRange('A1').setValue(response);
//other network requests which handle the selected data
//
//end other requests
var html = "<a href='https://www.MYWEBPAGE.com/SOMEOTHERPHPPAGE.php?coach=" + coach + "'
target='_blank'>Upload Program</a>"
//opening the URL
Logger.log('openUrl. html: ' + html);
var htmlOutput = HtmlService.createHtmlOutput(html).setWidth( 250 ).setHeight( 300 );
Logger.log('openUrl. htmlOutput: ' + htmlOutput);
SpreadsheetApp.getUi().showModalDialog( htmlOutput, `Assign Training Block to Athlete by
selecting hyperlink below` );
return