I added a new menu item to my spreadsheet using google apps script. This menu item creates a file, but I’d like for it to initiate the download of the file after creating it.
Is this possible?
Remember, this is not a web app, but a menu item in my spreadsheet.
Edit:
Thanks to Serge insas’ suggestion, the following simple script works perfectly, and opens a download window with the link I need:
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [ {name: "Zip all CSVs", functionName: "saveAsCSV"} ];
ss.addMenu("CSV", csvMenuEntries);
};
function saveAsCSV() {
var folder = createCSVs(); // creates a folder with CSV for each Sheet
var zipFile = zipCSVs(folder, "DI.zip"); // creates a zip of all CSVs in folder
var ui = UiApp.createApplication().setTitle("Download");
var p = ui.createVerticalPanel();
ui.add(p);
p.add(ui.createAnchor("Download", zipFile.getDownloadUrl()));
SpreadsheetApp.getActive().show(ui)
}
3
OP’s answer is deprecated (in 2021), so I made a more general purpose one based on it.
Code.gs:
// Runs when the spreadsheet starts, adds a tab at the top
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Script Menu')
.addItem('Download a file!', 'dlFile')
.addToUi();
}
// Run when you click "Download a file!"
function dlFile() {
let file = DriveApp.getRootFolder().createFile('Hi.txt', 'Hello, world!');
// Create little HTML popup with the URL of the download
let htmlTemplate = HtmlService.createTemplateFromFile('Download.html');
htmlTemplate.dataFromServerTemplate = { url: file.getDownloadUrl() };
let html = htmlTemplate
.evaluate()
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Download');
};
Download.html:
<!DOCTYPE html>
<html>
<head>
<script>
let data = <?!= JSON.stringify(dataFromServerTemplate) ?>; // Stores the data directly in the javascript code
function downloadFile() {
document.getElementById("dlBtn").innerText = "Downloading..";
window.open(data.url, '_blank');
document.getElementById("dlBtn").disabled = true;
}
</script>
</head>
<body>
<button id="dlBtn" onclick="downloadFile()">Download</button>
</body>
</html>
EDIT : read the comments below, Zig Mandel is perfectly right when he points out the limitations of the “complicated” version, it was really a simple (and fun) exercice to show other methods.
I think you’ll have to use an intermediate Ui as a popup to confirm the download.
After that there are 2 possible ways that I know, one is very simple and the other is quite cumbersome, make your choice, the code below shows both of them.
note : to use the complicated one you need to deploy your app (ie save a version and deploy as webapp), for the simple one just use it “as it is”. (I show the simple in the code comments).
The code :
function onOpen() {
var menuEntries = [ {name: "test download", functionName: "downloadFile"}
];
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.addMenu("Utils",menuEntries);
}
function downloadFile(){
var file = DriveApp.createFile('test file', 'Some content in this file to test it');
var fileID = file.getId();
var fileName = file.getName();
var ui = UiApp.createApplication().setTitle('Download');
var url = ScriptApp.getService().getUrl()+'?&ID='+fileID+'&name='+fileName;
var p = ui.createVerticalPanel();
ui.add(p);
p.add(ui.createAnchor('click to download', url));
p.add(ui.createAnchor('or use this link ',file.getDownloadUrl()));// this is the simple one, just get the file you created and use getDownloadUrl()
SpreadsheetApp.getActive().show(ui)
}
function doGet(e){
var fileId = e.parameter.ID;
var fileName = e.parameter.name;
var fileString = DocsList.getFileById(fileId).getContentAsString();
return ContentService.createTextOutput(fileString).downloadAsFile(fileName);
}
PS : I had some fun writing this, the “complicated version” is really funny imho 🙂
6
Just adding to @dr-bracket’s answer where I made some small additions to the scripts in an attempt to stop the browser from navigating away to a new tab.
I got the idea from:
Download a created Google Doc from a deployed web app (Google Apps Script)
Where @tanaike uses the google.script.run.withSuccessHandler class and method to create a popup prompt then closes and returns to your app on download. (May not popup if your browser settings are set to not pick download location.)
Code.gs:
// Runs when the spreadsheet starts, adds a tab at the top
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Script Menu')
.addItem('Download a file!', 'dlFile')
.addToUi();
}
// Run when you click "Download a file!"
function dlFile() {
let file = DriveApp.getRootFolder().createFile('Hi.txt', 'Hello, world!');
// Create little HTML popup with the URL of the download. Added filename to object. ~~~~~~~~~~~
let htmlTemplate = HtmlService.createTemplateFromFile('Download.html');
htmlTemplate.dataFromServerTemplate = { url: file.getDownloadUrl(), name: file.getName() };
let html = htmlTemplate
.evaluate()
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Download');
};
// Added the following to satisfy the withSuccessHandler method: ~~~~~~~~~~~~~
function createDownloadUrl(data) {
return {
url: data.url,
name: data.name,
};
}
Download.html:
<!DOCTYPE html>
<html>
<head>
<script>
let data = <?!= JSON.stringify(dataFromServerTemplate) ?>; // Stores the data directly in
// the javascript code
function downloadFile() {
const dlBtn = document.getElementById("dlBtn");
dlBtn.innerText = "Downloading..";
// window.open(data.url);
// Replaced with:
// the url and name variables will be returned here from the
// code.gs function createDownloadEvent() after it runs successfully.
google.script.run
.withSuccessHandler(({ url, name }) => {
const a = document.createElement("a");
document.body.appendChild(a);
a.download = name;
a.href = url;
a.target = "_blank";
a.click();
})
.createDownloadEvent(data);
dlBtn.disabled = true;
}
</script>
</head>
<body>
<button id="dlBtn" onclick="downloadFile()">Download</button>
</body>
</html>
Resources:
- https://developers.google.com/apps-script/guides/html/reference/run#withsuccesshandlerfunction
- Download a created Google Doc from a deployed web app (Google Apps Script)
Solution using data URI scheme without any additional clicking:
function downloadFile(fileName, mimeType, blob) {
const htmlOutput = HtmlService.createHtmlOutput(`<!DOCTYPE html>
<html>
<head>
<script>
window.onload = function() {
const a = document.createElement("a");
a.href = "data:${mimeType};base64,${Utilities.base64Encode(blob.getBytes())}";
a.download = "${fileName}";
a.click();
google.script.host.close();
};
</script>
</head>
<body>
</body>
</html>`);
SpreadsheetApp.getUi().showModalDialog(htmlOutput.setWidth(1).setHeight(1), 'Downloading...');
}
Also specialized for text files with optional UTF-8 BOM:
function downloadTextFile(fileName, mimeType, textContent) {
const utf8bom = true;
const blob = Utilities.newBlob([
...(utf8bom ? new Uint8Array([0xEF, 0xBB, 0xBF]) : []),
...Utilities.newBlob(textContent, "utf-8")
.getBytes()]);
downloadFile(fileName, mimeType, blob);
}