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):

sleep before children highlight/unhighlight

GrayedFox
  • 2,350
  • 26
  • 44
  • 1
    Write caching. Flush the buffer rather than sleep. – tehhowch Mar 25 '19 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 '19 at 13:33
  • Feel free to turn that comment into an answer and I will mark it as accepted :) – GrayedFox Mar 25 '19 at 13:33
  • Possible duplicate of [.setValue is only executed when the script is finished](https://stackoverflow.com/questions/49091172/setvalue-is-only-executed-when-the-script-is-finished) – tehhowch Mar 26 '19 at 14:06
  • Also related: https://stackoverflow.com/questions/51241213/display-the-updated-cell-values-in-google-spreadsheet-using-app-script https://stackoverflow.com/questions/50192942/why-doesnt-copyto-paste-values-work-in-the-middle-of-a-macro https://stackoverflow.com/questions/26925055/how-can-i-pause-the-execution-of-code-in-google-script-until-the-entire-spreadsh https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush (and others) – tehhowch Mar 26 '19 at 14:08

0 Answers0