0

I have a Google Sheets spreadsheet with a simple button that I've created via Insert>Drawing. I would like to assign a script to the button that when clicked will download a different tab/sheet as an excel file to the local computer.

I already found this post with a solution that achieves the same result that I'm looking for, but it does it via a modal dialog/UI object. It uses the HTML service.

*Edit: Here is the code found in the post linked to above:

 /**
 * Adds a custom menu
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Custom')
      .addItem('Download as XLSX', 'downloadXLS_GUI')
      .addToUi();
}


/**
 * Display a modal dialog with a single download link.
 *
 * From: http://stackoverflow.com/a/37336778/1677912
 */
function downloadXLS_GUI() {
  // Get current spreadsheet's ID, place in download URL
  var ssID = SpreadsheetApp.getActive().getId();
  var URL = 'https://docs.google.com/spreadsheets/d/'+ssID+'/export?format=xlsx';

  // Display a modal dialog box with download link.
  var htmlOutput = HtmlService
                  .createHtmlOutput('<a href="'+URL+'">Click to download</a>')
                  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
                  .setWidth(80)
                  .setHeight(60);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download XLS');
}

I have tried this solution and it does work, but I would like to know if this can be done with a simple button drawing instead; I.e., I want to assign the trigger to a shape that I can place on the sheet body itself instead of a custom menu added to the ribbon.

Any help would be much appreciated.

*Update: The idea above was to eliminate the need for the user to click a second time. After doing some more research it appears this is not possible unless the user changes their browser settings to allow popups. This is because by default, only a direct user action can open a URL. But my desired functionality would need to execute code which then would try to open the URL, so it is blocked by the browser. See more information here. So it seems this dialog box method is the best solution currently available.

DD990
  • 1
  • 1
  • 2

2 Answers2

0

Short answer: It's not possible by using only Google Apps Script.

That is because Google Apps Script can't interact with the user's local environment.

One option is to use Google Apps Script together with Google Drive for Desktop, i.e. by making script to create the XLSX file and move it to a folder set to be available offline.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Did you see the [answer by Mogsdad](https://stackoverflow.com/a/37336778/17845282) to the post I linked? Mogsdad explains that it can be done by leveraging the browser instead of server side Google Apps Scripts, which can't access the user's local environment, as you have mentioned. I just want to assign the trigger to a shape that I can place on the sheet body itself instead of a custom menu added to the ribbon. – DD990 Jan 06 '22 at 14:40
  • @DD990 While links might be helpful, questions should be self-contained. Considering this, please edit your question to make it clear what you have tried and what you are asking. – Rubén Jan 06 '22 at 18:21
  • Thanks for your advice. I have updated my post to include more information. I hope it is now sufficient as I don't believe I can make what I'm asking any more clear. – DD990 Jan 06 '22 at 20:43
0

From the question

I want to assign the trigger to a shape that I can place on the sheet body itself instead of a custom menu added to the ribbon.

Just assign downloadXLS_GUI to the shape. Please checkout the official guide for the detailed instructions about how to assign a function to shape in Google Sheets.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • The download_GUI function creates and displays a dialog box with linked text for the user to click. This would still require the user to click twice in order to download the sheet: once on the shape, then again on the dialog box text. I would like to eliminate the need for the second click. See the update at the bottom of my post. Thanks anyway for your suggestion. – DD990 Jan 07 '22 at 14:52