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.
CodePudding user response：
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.
CodePudding user response：
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