Skip to content

Instantly share code, notes, and snippets.

@dsottimano
Last active September 3, 2020 04:19
Show Gist options
  • Save dsottimano/00428387e312aca970d3cfbbf361396f to your computer and use it in GitHub Desktop.
Save dsottimano/00428387e312aca970d3cfbbf361396f to your computer and use it in GitHub Desktop.
Beating the apps script custom function 30 second time limit. Alpha? Beta? :P
//David Sottimano
//@dsottimano on Twitter for feedback please!
//lets set up our queue runner to run every 1 minutes
function onOpen() {
ScriptApp.newTrigger('queueRunner')
.timeBased()
.everyMinutes(1)
.create();
}
//normally returns an array with hello world
//if queue param is true, we ship off the function details along with where it was called to the propertyservice
/**
* returns hello world in 2 cols
*
* @param {true} queue boolean - if true, you put it in the queue
* @customfunction
*/
function dostuff(queue) {
if (queue) {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell()
let col = sheet.getColumn()
let row = sheet.getRow()
let sheetname = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();
let callerObj = {
name: arguments.callee.name,
args: arguments,
row: row,
col: col,
sheetname: sheetname
}
let data = JSON.stringify(callerObj);
//lets store it as run: plus a kinda unique property, this needs to be thought out better than just base64 encoding the formula because there can be rare dupes?
PropertiesService.getUserProperties().setProperty("run:"+ Utilities.base64Encode(data), data)
//we return info right away to the user and now this function has returned.
return "this function will run in 1 minute, here's the details: " + data
}
return [["hello","world"]];
}
//this function runs every minute, it's our queue runner
//this function gets 6 minutes to execute, so each function cumulatively needs to execute within 6 minutes, right?
function queueRunner() {
//there's only going to be one property for this proof of concept, todo!
let keys = PropertiesService.getUserProperties().getKeys()
for (let i in keys) {
if (keys[i].includes("run:")) {
let obj = JSON.parse(PropertiesService.getUserProperties().getProperty(keys[i]))
let e = obj.name.toString() + "()"
let returnValue = eval(e)
//uncomment to simulate a 2 minute function
//Utilities.sleep(120000);
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(obj.sheetname).activate()
let value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(obj.sheetname).getRange(obj.row, obj.col, returnValue.length, returnValue[0].length).setValues(returnValue)
PropertiesService.getUserProperties().deleteProperty(keys[i])
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment