Styjun

Output of "$OSTYPE:6" on old releases of Mac OS X

Who are the remaining King/Queenslayers?

Array initialization optimization

Do I have any obligations to my PhD supervisor's requests after I have graduated?

Inaccessible base class despite friendship

Has there been any indication at all that further negotiation between the UK and EU is possible?

Helping ease my back pain when I'm studying 13 hours everyday, even weekends

.NET executes a SQL query and Active Monitor shows multiple rows blocking each other

How to draw this center trajectory of rolling ball?

How much will studying magic in an academy cost?

How do I turn off a repeating trade?

Unusual mail headers, evidence of an attempted attack. Have I been pwned?

How large would a mega structure have to be to host 1 billion people indefinitely?

Greeting with "Ho"

What can I do with a research project that is my university’s intellectual property?

Did the CIA blow up a Siberian pipeline in 1982?

Can there be an UN resolution to remove a country from the UNSC?

Can any NP-Complete Problem be solved using at most polynomial space (but while using exponential time?)

Is it damaging to turn off a small fridge for two days every week?

Suggested order for Amazon Prime Doctor Who series

How does the spell Remove Curse interact with a Sword of Vengeance?

How does DC work with natural 20?

What does the hyphen "-" mean in "tar xzf -"?

Why does Linux list NVMe drives as /dev/nvme0 instead of /dev/sda?

Why is this Utilities.sleep executing before the methods before and after it?

Why do we use SpreadsheetApp.flush();?How can I pause the execution of code in google script until the entire spreadsheet gets updated?Why doesn't copyTo(… PASTE_VALUES) work in the middle of a macro?.setValue is only executed when the script is finishedDisplay the updated cell values in google spreadsheet using app scriptWhy must wait() always be in synchronized blockHow to use Utilities.sleep() functionCustomise email before sendingJavaScript sleep/wait before continuingGoogle Apps Script close UI App after executionWhen executed by someone else, the script execution does not resume after a call to Browser.msgBox()Proper way to deal with UrlFetch rate limit without random Utilities.sleep?Why my script returns 'Missing ) after argument list.'?Utilities.sleep not running as expectedScript properties becomes corrupt: Failed to save Project Properties for script

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have the following code for a Google Sheet which utilises a Utilities.sleep() call.

Problematically, both highlightChildren() and unhighlightChildren() are executed after the Utilities.sleep() call and I can't figure out why.

function onOpen() 
 var ui = SpreadsheetApp.getUi()
 ui.createMenu('OKRs').addItem('Show linked Key Results', 'getLinkedKeyResults').addToUi()


var highlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffd966')


var unhighlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffffff')


function extendChildrenRefs(stringRefs) 
 return stringRefs.replace(/C/gi, 'B') + ',' + stringRefs


function getLinkedKeyResults() 
 var scriptProperties = PropertiesService.getScriptProperties()
 var ss = SpreadsheetApp.getActiveSpreadsheet()

 var parentRef = ss.getActiveCell().getA1Notation()
 var children = extendChildrenRefs(scriptProperties.getProperty(parentRef)).split(',')

 highlightChildren(children)
 Utilities.sleep(4 * 1000)
 unhighlightChildren(children)


/**
 * Links a parent key result progress to child key results, storing them in memory. Otheriwse, a direct clone of AVERAGE().
 *
 * @param "value1, value2, value3..." values The child key result values to consider when calculating parent key result progress.
 * @return The average of range of value.
 * @customfunction
 */
function LINKKEYRESULTS(values) 
 if (arguments.length < 2) throw new Error('You must pass at least two key result values!')

 var ss = SpreadsheetApp
 var scriptProperties = PropertiesService.getScriptProperties()
 var formula = ss.getActiveRange().getFormula()

 var args = formula.match(/=w+((.*))/i)

 var parentRef = ss.getActiveRange().getA1Notation()
 var childrenRefs = args[1]

 scriptProperties.setProperty(parentRef, childrenRefs)

 var children = childrenRefs.split(',')

 var sum = 0

 children.forEach( function(child) 
 sum += ss.getActiveSheet().getRange(child).getValue()
 )

 return sum / children.length

The following GIF shows the buggy behaviour. Note that I commented out the unhighlightChildren method call in order to show the buggy behaviour. If I leave it in you wouldn't see the highlight, since after 4 seconds the highlight is triggered but is immediately followed by unhighlight, which makes it appear as if neither method is called (which is not true):

google-apps-script google-sheets wait

share|improve this question

edited Mar 25 at 9:00

GrayedFox

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Write caching. Flush the buffer rather than sleep.
  
  – tehhowch
  Mar 25 at 10:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You, good sir, have just saved me looking like a damn fool in my upcoming presentation. Thank you, that fixed it. I've inserted the flush just before the sleep and I now have the desired behaviour (a temporary linke KR highlight!)
  
  – GrayedFox
  Mar 25 at 13:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Feel free to turn that comment into an answer and I will mark it as accepted :)
  
  – GrayedFox
  Mar 25 at 13:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of .setValue is only executed when the script is finished
  
  – tehhowch
  Mar 26 at 14:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also related: stackoverflow.com/questions/51241213/… stackoverflow.com/questions/50192942/… stackoverflow.com/questions/26925055/… stackoverflow.com/questions/41175326/… (and others)
  
  – tehhowch
  Mar 26 at 14:08
  
  

  
  
  
  

add a comment | 

0

I have the following code for a Google Sheet which utilises a Utilities.sleep() call.

Problematically, both highlightChildren() and unhighlightChildren() are executed after the Utilities.sleep() call and I can't figure out why.

function onOpen() 
 var ui = SpreadsheetApp.getUi()
 ui.createMenu('OKRs').addItem('Show linked Key Results', 'getLinkedKeyResults').addToUi()


var highlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffd966')


var unhighlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffffff')


function extendChildrenRefs(stringRefs) 
 return stringRefs.replace(/C/gi, 'B') + ',' + stringRefs


function getLinkedKeyResults() 
 var scriptProperties = PropertiesService.getScriptProperties()
 var ss = SpreadsheetApp.getActiveSpreadsheet()

 var parentRef = ss.getActiveCell().getA1Notation()
 var children = extendChildrenRefs(scriptProperties.getProperty(parentRef)).split(',')

 highlightChildren(children)
 Utilities.sleep(4 * 1000)
 unhighlightChildren(children)


/**
 * Links a parent key result progress to child key results, storing them in memory. Otheriwse, a direct clone of AVERAGE().
 *
 * @param "value1, value2, value3..." values The child key result values to consider when calculating parent key result progress.
 * @return The average of range of value.
 * @customfunction
 */
function LINKKEYRESULTS(values) 
 if (arguments.length < 2) throw new Error('You must pass at least two key result values!')

 var ss = SpreadsheetApp
 var scriptProperties = PropertiesService.getScriptProperties()
 var formula = ss.getActiveRange().getFormula()

 var args = formula.match(/=w+((.*))/i)

 var parentRef = ss.getActiveRange().getA1Notation()
 var childrenRefs = args[1]

 scriptProperties.setProperty(parentRef, childrenRefs)

 var children = childrenRefs.split(',')

 var sum = 0

 children.forEach( function(child) 
 sum += ss.getActiveSheet().getRange(child).getValue()
 )

 return sum / children.length

The following GIF shows the buggy behaviour. Note that I commented out the unhighlightChildren method call in order to show the buggy behaviour. If I leave it in you wouldn't see the highlight, since after 4 seconds the highlight is triggered but is immediately followed by unhighlight, which makes it appear as if neither method is called (which is not true):

google-apps-script google-sheets wait

share|improve this question

edited Mar 25 at 9:00

GrayedFox

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Write caching. Flush the buffer rather than sleep.
  
  – tehhowch
  Mar 25 at 10:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You, good sir, have just saved me looking like a damn fool in my upcoming presentation. Thank you, that fixed it. I've inserted the flush just before the sleep and I now have the desired behaviour (a temporary linke KR highlight!)
  
  – GrayedFox
  Mar 25 at 13:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Feel free to turn that comment into an answer and I will mark it as accepted :)
  
  – GrayedFox
  Mar 25 at 13:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of .setValue is only executed when the script is finished
  
  – tehhowch
  Mar 26 at 14:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also related: stackoverflow.com/questions/51241213/… stackoverflow.com/questions/50192942/… stackoverflow.com/questions/26925055/… stackoverflow.com/questions/41175326/… (and others)
  
  – tehhowch
  Mar 26 at 14:08
  
  

  
  
  
  

add a comment | 

I have the following code for a Google Sheet which utilises a Utilities.sleep() call.

Problematically, both highlightChildren() and unhighlightChildren() are executed after the Utilities.sleep() call and I can't figure out why.

function onOpen() 
 var ui = SpreadsheetApp.getUi()
 ui.createMenu('OKRs').addItem('Show linked Key Results', 'getLinkedKeyResults').addToUi()


var highlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffd966')


var unhighlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffffff')


function extendChildrenRefs(stringRefs) 
 return stringRefs.replace(/C/gi, 'B') + ',' + stringRefs


function getLinkedKeyResults() 
 var scriptProperties = PropertiesService.getScriptProperties()
 var ss = SpreadsheetApp.getActiveSpreadsheet()

 var parentRef = ss.getActiveCell().getA1Notation()
 var children = extendChildrenRefs(scriptProperties.getProperty(parentRef)).split(',')

 highlightChildren(children)
 Utilities.sleep(4 * 1000)
 unhighlightChildren(children)


/**
 * Links a parent key result progress to child key results, storing them in memory. Otheriwse, a direct clone of AVERAGE().
 *
 * @param "value1, value2, value3..." values The child key result values to consider when calculating parent key result progress.
 * @return The average of range of value.
 * @customfunction
 */
function LINKKEYRESULTS(values) 
 if (arguments.length < 2) throw new Error('You must pass at least two key result values!')

 var ss = SpreadsheetApp
 var scriptProperties = PropertiesService.getScriptProperties()
 var formula = ss.getActiveRange().getFormula()

 var args = formula.match(/=w+((.*))/i)

 var parentRef = ss.getActiveRange().getA1Notation()
 var childrenRefs = args[1]

 scriptProperties.setProperty(parentRef, childrenRefs)

 var children = childrenRefs.split(',')

 var sum = 0

 children.forEach( function(child) 
 sum += ss.getActiveSheet().getRange(child).getValue()
 )

 return sum / children.length

The following GIF shows the buggy behaviour. Note that I commented out the unhighlightChildren method call in order to show the buggy behaviour. If I leave it in you wouldn't see the highlight, since after 4 seconds the highlight is triggered but is immediately followed by unhighlight, which makes it appear as if neither method is called (which is not true):

google-apps-script google-sheets wait

share|improve this question

edited Mar 25 at 9:00

GrayedFox

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

function onOpen() 
 var ui = SpreadsheetApp.getUi()
 ui.createMenu('OKRs').addItem('Show linked Key Results', 'getLinkedKeyResults').addToUi()


var highlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffd966')


var unhighlightChildren = function(rangeList) 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeList(rangeList).setBackground('#ffffff')


function extendChildrenRefs(stringRefs) 
 return stringRefs.replace(/C/gi, 'B') + ',' + stringRefs


function getLinkedKeyResults() 
 var scriptProperties = PropertiesService.getScriptProperties()
 var ss = SpreadsheetApp.getActiveSpreadsheet()

 var parentRef = ss.getActiveCell().getA1Notation()
 var children = extendChildrenRefs(scriptProperties.getProperty(parentRef)).split(',')

 highlightChildren(children)
 Utilities.sleep(4 * 1000)
 unhighlightChildren(children)


/**
 * Links a parent key result progress to child key results, storing them in memory. Otheriwse, a direct clone of AVERAGE().
 *
 * @param "value1, value2, value3..." values The child key result values to consider when calculating parent key result progress.
 * @return The average of range of value.
 * @customfunction
 */
function LINKKEYRESULTS(values) 
 if (arguments.length < 2) throw new Error('You must pass at least two key result values!')

 var ss = SpreadsheetApp
 var scriptProperties = PropertiesService.getScriptProperties()
 var formula = ss.getActiveRange().getFormula()

 var args = formula.match(/=w+((.*))/i)

 var parentRef = ss.getActiveRange().getA1Notation()
 var childrenRefs = args[1]

 scriptProperties.setProperty(parentRef, childrenRefs)

 var children = childrenRefs.split(',')

 var sum = 0

 children.forEach( function(child) 
 sum += ss.getActiveSheet().getRange(child).getValue()
 )

 return sum / children.length

share|improve this question

edited Mar 25 at 9:00

GrayedFox

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

share|improve this question

edited Mar 25 at 9:00

GrayedFox

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229

asked Mar 25 at 8:47

GrayedFoxGrayedFox

7741229