Google-sheets – How to test apps script addon

google sheetsgoogle-apps-scriptgoogle-sheets-addons

I have created an addon using this guide.

code.gs

function onOpen() {
   SpreadsheetApp.getUi().createAddonMenu()
   .addItem('Start', 'showSidebar');
}

function showSidebar() {
  const ui = HtmlService.createHtmlOutputFromFile('index').setTitle('Sample');
  SpreadsheetApp.getUi().showSidebar(ui);
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Works</h1>
  </body>
</html>

It says "If you switch back to document and reload you will see a submenu under Add-ons menu.

But I could not find my addon submenu, I think the quickstart guide is outdated.

Please guide me, How to test the addon on google sheet.

Best Answer

The problem is that the onOpen function is incomplete; .addToUi(); is missing.

This is the onOpen function on the referred guide

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 * This method is only used by the regular add-on, and is never called by
 * the mobile add-on version.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
  DocumentApp.getUi().createAddonMenu()
      .addItem('Start', 'showSidebar')
      .addToUi();
}

Related Solutions

Google Apps Script – Fixing Maximum Execution Time Exceeded

It's better not to call setValue in a loop, but instead form a double array of values to be inserted, and put them all at once with setValues. This often helps with keeping resource use under quota.

However, in your case the main issue is not setValue but UrlFetchApp.fetch in a loop. Hitting some API a bunch of times in a loop is an expensive thing to do, both for your script and the API provider. The maximum execution time of Apps Script is 6 minutes, after which the script is terminated with the message you quoted. To avoid this, follow Anton Soradoi's advice:

Store the necessary information (i.e. like a loop counter) in a spreadsheet or another permanent store (i.e. ScriptProperties).

Have your script terminate every five minutes or so.

Set up a time driven trigger to run the script every five minutes(or create a trigger programmatically using the Script service).

On each run read the saved data from the permanent store you've used and continue to run the script from where it left off.

Terminating a script means recording the starting time start = Date.now(), and then checking in the loop whether you are running over 5 minutes (start+5*60*1000). If this happens, exit the loop (hence the script) but set a trigger to resume work in a few minutes. Since you already work with a spreadsheet and record the results there, it will not be difficult to find the place where the script should pick up the work after restart.

Google-sheets – Speeding up setFormula Google Apps Script

Any function that operates on actual spreadsheet cells, are bound to be slow. So use them as little as possible. In your script, you have 19 such calls: getRange, setNumberFormats and setFormula.

Instead of getting 10 different ranges, and calling setFormula and setNumberFormat on each of them, get a single range, alter it, and write it back, using setNumberFormats and setFormulas (note the plural form).

There are API functions for getting arrays of existing formulas and number formats. This could serve as an example:

var range = sheet.getDataRange(); // Get a single, large range of all the cells in the sheet
var formulas = range.getFormulas();
var numberFormats = range.getNumberFormats();

// This script operates on two different 'ranges', as an example. Write similar FOR blocks for operations you need to perform.

// Example: Applying formatting and a formula to G4:G1000. 
for (var row = 3; row < 1000; row++) { // the array is 0-based, so 3 refers to row 4
   var column = 6; // refers to column G 
   formulas[row][column] = '=IF(G4="","",WEEKNUM(G4)&"/"&YEAR(G4))';
   numberFormats[row][column] = 'yyyy-mm-dd';
}

// Example: Applying formatting and formula to V4:V1000
for (var row = 3; row < 1000; row++) { // the array is 0-based, so 3 refers to row 4
   var column = 21; // refers to column V
   formulas[row][column] = '=IFERROR(SUMPRODUCT(--($K$2:$T$2=V$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,V$2,$K4:$T4,"<>N/A"),"")'
   numberFormats[row][column] = "0.0%";
}

range.setFormulas(formulas); // Write the formulas to the actual cells
range.setNumberFormats(numberFormats); // Apply number formats to the actual cells

So here we have limited the number of API calls to 4:

Get an array of existing formulas
Get an array of existing number formats
Write back an array of formulas
Write back an array of number formats

... which should improve speed significantly.

See the documentation for setFormulas and setNumberFormats

Best Answer

Related Solutions

Google Apps Script – Fixing Maximum Execution Time Exceeded

Google-sheets – Speeding up setFormula Google Apps Script

Related Topic