Google App Script ContentService downloadAsFile no

问题:

I have a web app developed using Google App Script HtmlService and from the html form, populating excel sheet in the Google drive using SpreadsheetApp. And one another section is calling ContentService to download data as excel file.

function doGet(e) {
  // Read excel sheet
  //getAppFile();
  // Render the application from HTML template
  return HtmlService.createTemplateFromFile('index').evaluate()
    .setTitle('Go Smart')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function downloadDoubleQuateCsvFile() {
  var sheetId =  PropertiesService.getScriptProperties().getProperty('sheetId');
  var ss = SpreadsheetApp.openById(sheetId).getActiveSheet();
    //var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var maxColumn = ss.getLastColumn();
    var maxRow = ss.getLastRow();
    var data = ss.getRange(1, 1, maxRow, maxColumn).getValues();
    if (data.length > 1) {
        var csv = "";
        for (var row = 0; row < data.length; row++)  {
            for (var col = 0; col < data[row].length; col++) {
                if (data[row][col].toString().indexOf(",") != - 1) {
                    data[row][col] = "\"" + data[row][col] + "\"";
                }
            }

            if (row < data.length - 1) {
                csv += data[row].join(",") + "\r\n";
            } else {
                csv += data[row];
            }
        }

        csvFile = csv;
    }

    return makeCSV(csvFile);
}

function makeCSV(csvString) {
    var csvFileName = 'test.csv';
    var output = ContentService.createTextOutput();
    output.setMimeType(ContentService.MimeType.CSV);
    output.setContent(csvString);
    output.downloadAsFile(csvFileName);
    return output;
}

This script is just giving the sheet header details object in console and it is not downloading any file.

<button class="btn btn-success btn-sm" onclick="google.script.run.downloadDoubleQuateCsvFile()">Export</button>

After adding return in the second function, I am getting error like this.

Error: The script completed but the returned value is not a supported return type.

Note: I have the excel file in drive with data

回答1:

To get downloadAsFile() method to work the contentservice object has to be returned from a doGet() or a doPost() called from the published URL.

Example:

 function doGet(){
        var output = ContentService.createTextOutput();
        output.setMimeType(ContentService.MimeType.CSV);
        output.setContent(csvString);
        output.downloadAsFile(csvFileName);
        return output;
}

In your code you are returning the ContentService object to a webpage via google.script.run. It will not request a download from the browser. In fact returning a contentservice object will result in an error as it is not a valid object to return to a google.script.run call. Only native javascript objects are allowed.

If you want it to work you would need to present the users with a link to click that would point to your script in another tab. Or you can use the 'download' attribute on an anchor tag pointing to your script.

For example, and this assumes you keep the return fix to downloadDoubleQuateCsvFile():

function doGet(e){
   var serveCSV = e.parameter.servecsv;
   if(serveCSV){return downloadDoubleQuateCsvFile()}
   return HtmlService.createTemplateFromFile('index').evaluate()
.setTitle('Go Smart')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

In your webpage:

<a href="//script.google.com/WebAppURL/exec?servecsv=true" target="_blank">Click here to download</a>

Remeber that this is not supported in all browsers. (Think only chrome,opera,firefox supports autodownload).