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?

Answer 1

1. 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.

2. 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);
   
   }
   

3. 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.

4. 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.