Zip all spreadsheets as CSV and send as email

Question

I'm using Google Sheets to create data for a program I'm working on. What I do is use Google Sheets to build the data, and then download as CSV, and copy it to the application folder.

The problem is that now I have quite a few sheets (tabs) in my data and downloading them one by one is a bit of a hassle.

So I thought I'd use Google Apps Script to create a function that will convert them all to CSV, zip them, and send them to the client as a download.

Is this doable? Anyone have any suggestions as to how?

Yes its doable. Take a look at spreadsheetapp and come back when you have some code. — Zig Mandel, Nov 28 '13 at 14:54
I get using the API to create a CSV string (or multiple for all my sheets the way I want). How do I take all these CSV strings and create files out of them, zip these files together, and then send to browser for download? — tbkn23, Nov 29 '13 at 06:40

score 4 · Accepted Answer · answered Nov 28 '13 at 15:26

4

Convert sheet to CSV

See Saving a selected Range to a CSV file from Tutorial: Interacting With Your Docs List.

If you create a temporary folder for this, then generate the CSV files in that folder, you will simplify the later zip and clean up operations.

Zip multiple files from drive

See Utilities.zip(blobs, name). If all the zip files are in a temporary folder, then you should be able to do something like this:

function zipSheets() {
  var name = 'All CSV Files.zip';
  var folderName = 'Temp CSV Files';

  var folder = DriveApp.getFoldersByName(folderName).next();
  var csvFilesIterator = folder.getFiles();
  var csvFiles = [];

  while (csvFilesIterator.hasNext()) {
    csvFiles.push(csvFilesIterator.next().getBlob());
  }

  // Create zip file on google drive
  var zipBlob = Utilities.zip(csvFiles, name);
  var zipFile = DriveApp.createFile(zipBlob);
  // NOTE: sharing permissions not set

  // get URL of zip file, and display to user.
  // ASSUMES you've launched this from a spreadsheet menu
  var url = DriveApp.getFilesByName(name).next().getUrl();

  var htmlApp = HtmlService
     .createHtmlOutput('<p>Zip file URL: '+url+'</p>')
     .setTitle('Zip file created')
     .setWidth(800)
     .setHeight(70);

  SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);

}

Email client the download URL for the newly created zip file.

See File.getUrl(), use it to get the URL of the zip file. (The example above demonstrates this function.) Embed that in the email, so client can click on it. When clicked, they will access the zip file from google drive, so you should also ensure that the sharing is set appropriately.

Alternatively, you could attach the zip file to the email, and thereby avoid sharing from your Drive.
Clean up temporary CSV files & folders.

If you chose to email the zip file as an attachment, you may also delete the temporary zip file.

answered Nov 28 '13 at 15:26

Mogsdad

44,709
21
151
275

Thanks, I'll get to it. One thing though, the first link has the note: `Note: This section uses the DocsList.createFile() method, which is only available for Google Apps accounts`. I'm using google drive privately, not as a business. Will that be a problem? – tbkn23 Nov 29 '13 at 06:10
Yep, getting `ReferenceError: "DocList" is not defined.` error... Any alternatives? – tbkn23 Nov 29 '13 at 06:32
Ok, I found DriveApp which seems to be able to create files, folders, etc in google drive without apps. BTW, it seems DocList is deprecated. – tbkn23 Nov 29 '13 at 06:51
This works mostly well. The only problem is that the CSV format in the example link you posted is not good enough. For example, it doesn't add quotes when there's a newline in the cell... The "Download as CSV" option of the spreadsheet does this better. Is there a way to use the same method to convert to CSV that "download as CSV" does? I sometimes download a single sheet and not all of them, so I prefer if they are the same. – tbkn23 Nov 29 '13 at 07:36
Unfortunately, there is no way for us scripters to get at the code used by the UI (or in Spreadsheet functions). You should be able to adapt the tutorial example to your specific needs - for example, check if text contains newlines or quotes, and encapsulate it. – Mogsdad Nov 29 '13 at 15:14
I see... well I hoped they provided the convert to CSV method as an API. Well, I guess I'll have to add new functionality as I need them. Thanks! – tbkn23 Nov 29 '13 at 16:09
BTW: see here http://stackoverflow.com/questions/20281272/initiate-a-download-from-google-apps-script/20284386?noredirect=1#20284386 for a nice solution on how to initiate a download without having to send an email, open your email client, etc... – tbkn23 Nov 29 '13 at 16:10

Zip all spreadsheets as CSV and send as email

1 Answers1