Last active
September 3, 2020 04:19
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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