CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Sharert Fukae
Sharert Fukae

Reputation: 75

GAS withSuccessHandler working for some functions but not others

I have this project attached to a spreadsheet:

HTML.html:

<!DOCTYPE html>
<html>
  <head>
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
   <script>
function script2() {
    google.script.run.doSomething();
}
function RunUpdateYearsManual() {
google.script.run.withSuccessHandler(script2).resetIndicatorsUpdateYears();
}
   </script>
  </head>
  <body>
 <input type="button" value="Update" onClick="RunUpdateYearsManual();" />
 <br><p></p>

  </body>
</html>

Code.gs:

function doSomething() {
  Logger.log('I was called! CALLED');
}

function resetIndicatorsUpdateYears() {
var newD = new Date();
var hour = newD.getHours();
var minute = newD.getMinutes();
var second = newD.getSeconds();
var s3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Intro")
s3.getRange("F29").setNote("Started last update cycle on: "+ newD);
s3.getRange("F31:F46").setValue("Skipped").clearFormat();
s3.getRange("F30").setValue("Waiting").clearFormat();
s3.getRange("F30").setNote("Waiting for Year and English Lists Update.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F31").setNote("Skipped Company Lists Update 0001-0350.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F32").setNote("Skipped Company Lists Update 0351-0700.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F33").setNote("Skipped Company Lists Update 0701-1050.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F34").setNote("Skipped Company Lists Update 1051-1400.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F35").setNote("Skipped Company Lists Update 1401-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F36").setNote("Skipped Bundled Games In Year and English Lists Update.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F37").setNote("Skipped Bundled Games In Company Lists Removal 0001-0600.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F38").setNote("Skipped Bundled Games In Company Lists Removal 0601-1200.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F39").setNote("Skipped Bundled Games In Company Lists Removal 1201-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F40").setNote("Skipped Empty Rows Sorting 0001-0400.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F41").setNote("Skipped Empty Rows Sorting 0401-0800.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F42").setNote("Skipped Empty Rows Sorting 0801-1200.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F43").setNote("Skipped Empty Rows Sorting 1201-1600.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F44").setNote("Skipped Empty Rows Sorting 1601-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F45").setNote("Skipped Empty Rows Removal 0001-0900.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
s3.getRange("F46").setNote("Skipped Empty Rows Removal 0900-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second); 
deleteFormulas();
  Logger.log('I was called! TEST');
}

But this does not seem to work, I run RunUpdateYearsManual and all it does is execute the first function without ever starting the second, the logger will only return "I was called! TEST", there's no failure and using withFailureHandler does not help.

But here's the weird part about it, if I remove those lines editing F30:F46 in my code and make it something like this:

Code.gs:

function doSomething() {
  Logger.log('I was called! CALLED');
}

function resetIndicatorsUpdateYears() {
var newD = new Date();
var hour = newD.getHours();
var minute = newD.getMinutes();
var second = newD.getSeconds();
var s3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Intro")
s3.getRange("F29").setNote("Started last update cycle on: "+ newD);
  Logger.log('I was called! TEST');
}

This now works and I see a "I was called! CALLED" in the logger.

So I'm completely lost here. I imagine it might be some sort of limitation for the original script being longer? But that takes less than 20 seconds to run and the only documentation that comes close that I can find is Custom function runtime that's limited to 30 seconds/execution, don't think that's the case here though.

Any help here would be appreciated.

Upvotes: 1

Views: 175

Answers (2)

Sharert Fukae
Sharert Fukae

Reputation: 75

With the help of users over at the Google forums (https://plus.google.com/111489556118142836691/posts/53tX2fW6D6M) I have managed to find and fix the issue.

Basically, you need to leave the UI window you used to execute the functions open, a window will obviously block you from working that well on the spreadsheet so a sidebar UI will work just as well, the UI that you used to trigger the first function just needs to stay open. If you close that UI at any point the current function will finish running but it won't trigger any other.

Not entirely sure why this is the case but that seems to settle things.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201553

How about the following modification? In this modified script, I tried to be shortening of the process time using setNotes() for resetIndicatorsUpdateYears(). Please try this modified sample.

The detail information of setNotes() is here.

Modified script :

function resetIndicatorsUpdateYears() {
  var newD = new Date();
  var hour = newD.getHours();
  var minute = newD.getMinutes();
  var second = newD.getSeconds();
  var s3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Intro")
  s3.getRange("F29").setNote("Started last update cycle on: "+ newD);
  s3.getRange("F31:F46").setValue("Skipped").clearFormat();
  s3.getRange("F30").setValue("Waiting").clearFormat();
  var data = [["Waiting for Year and English Lists Update.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Company Lists Update 0001-0350.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Company Lists Update 0351-0700.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Company Lists Update 0701-1050.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Company Lists Update 1051-1400.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Company Lists Update 1401-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Bundled Games In Year and English Lists Update.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Bundled Games In Company Lists Removal 0001-0600.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Bundled Games In Company Lists Removal 0601-1200.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Bundled Games In Company Lists Removal 1201-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Sorting 0001-0400.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Sorting 0401-0800.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Sorting 0801-1200.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Sorting 1201-1600.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Sorting 1601-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Removal 0001-0900.\n\n\n"+"Started at: "+hour+":"+minute+":"+second],
  ["Skipped Empty Rows Removal 0900-XXXX.\n\n\n"+"Started at: "+hour+":"+minute+":"+second]];
  s3.getRange("F30:F46").setNotes(data);
  // deleteFormulas();
  Logger.log('I was called! TEST');
}

Upvotes: 0

Related Questions