Since a few months ago, users who make a copy are confronted by a "This app is not verified" screen when authorising the App Script to run. I understand that this is due to Google changing the security, but I can't figure out a way to properly get rid of the unverified status. This is what I tried:
- Verify my project as the developer. This did not work, since when people make a copy of the spreadsheet it is a new project which they own themselves - and naturally other users haven't verified the project when they just created it by copying it.
- Add
@OnlyCurrentDocto the top of the spreadsheet. This technically works, but only adding@OnlyCurrentDoccauses the run-time of the script to go from 4 seconds to 180 seconds.
A copy of the spreadsheet can be made at https://wowaudit.com/copy, and this is the code of the script that triggers the "Unverified App" message and takes so much longer to run with the @OnlyCurrentDoc snippet added:
/**
* @OnlyCurrentDoc
*/
function CreateTrigger() {
// First delete old triggers
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
ScriptApp.deleteTrigger(allTriggers[i]);
}
// Then create new triggers
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var interval = sheet.getRange("RefreshSetting").getValue().slice(0,2);
ScriptApp.newTrigger("Fetch").timeBased().everyMinutes(interval).create();
ScriptApp.newTrigger("Refresh").forSpreadsheet(sheet).onEdit().create();
// Then give user feedback of success
if (ScriptApp.getProjectTriggers().length == 2) {
sheet.getRange('Settings!B24').setValue('The sheet is automatically refreshing!');
sheet.getRange('Settings!B24').setBackground('#b6d7a8');
} else {
sheet.getRange('Settings!B24').setValue('Click here to start automatic refreshing!');
sheet.getRange('Settings!B24').setBackground('#ea9999');
}
}
Does anyone know of a method to stop the "Unverified App" message from appearing in my scenario, or why the @OnlyCurrentDoc flag would slow down the script so much (basically making it unusable)?
Details:
I have a spreadsheet that fetches data from an API through an App Script on a time-based trigger, which other users can make a copy of to see their own data (thousands of people are using it). The script is very simple, it fetches data through UrlFetchApp.fetch() and puts it in the spreadsheet, and it has a function that people can invoke to set up the time-based trigger after they first copy the spreadsheet (triggers can't be copied).
