Google Apps Script - batch download sheets as CSV file

Question

This is a question that has been asked before but I'm struggling to adapt the answers to my needs.

references:

• How to export to CSV from spreadsheet to drive or download folder
• https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1
• Initiate a download from google apps script

The structure seems to be:

1. create function in code.gs
2. create download.html

My workflow: I have a bunch of files (20+) which I edit both manually and using a GAS.

To start with I've created a folder on Google Drive which I upload my CSV files to. I then run a standalone GAS to add a sum formula in column F to each file:

function addFormula() {
  const folder    = DriveApp.getFolderById('folderID'); 
  const list      = [];
  const files     = folder.getFiles();
  while (files.hasNext()){
    file          = files.next();
    let ssnameid  = [];
    ssnameid.push(file.getName(),file.getId());
    list.push(ssnameid);
  }

  for (let g=0,len=list.length;g<len;g++) { 
   let id           = list[g][1]; // list of Spreadsheet names[0] and ID numbers[1]
   let csv          = DriveApp.getFileById(id);
   let docName      = csv.getName();
   let ss           = SpreadsheetApp.openById(id);
   let sheet        = ss.getSheetByName(docName+'.csv');
   let contents     = sheet.getRange('A1:K100').getValues().filter(e => e[0]);
   let rows         = contents.length;
   console.log(docName+' - number of rows: '+rows);
   let cellF        = 'F'+(rows+1);
   let formulaF     = '=SUM($F$2:$F$'+rows+')';
   sheet.getRange(cellF).setValue(formulaF);
}

Then I go through each file, check if there are any other edits I need to make, and download as a CSV (File > Download > Comma Separated Values (.csv)). I was hoping to save time by also writing a function to download all the files as CSV.

So after making any manual edits, I then want to run a function in a standalone GAS to download all the files in the Google Drive folder as CSV files.

The answers I've found generally involve adding menu items and having pop-ups, and I don't know enough to make them suitable for a standalone GAS - I don't want any menu items or pop-ups, I just want to run a function which downloads a CSV.

For instance, how would I adapt this answer from Dr-Bracket?

Or this answer from soMarios, which works but only saves it to another folder in Google Drive, rather than downloading.

The reason I feel that having an HTML file work with a GS is that I've created a standalone function with this structure to send out emails. Using an HTML email template, I created a function in a standalone GAS to send out emails.

Is this the right approach for batch downloading files as CSV?

Thank you

Further references/clues:

https://developers.google.com/apps-script/guides/html/templates#code.gs https://developers.google.com/apps-script/guides/html/reference/run#index.html https://developers.google.com/apps-script/reference/drive/file#getDownloadUrl()

EDIT - My Solution

The workaround is to send all the files to a folder on Google Drive and then download the folder. So the benefit is only downloading one folder rather than downloading each file. Here's the code adapted from the soMarios answer linked to above:

function saveCSV() {
  /** sourceFolder contains all the Google Sheets you want to save as CSV files */
  const sourceFolder   = DriveApp.getFolderById('folderID');
  const list           = [];
  const files          = sourceFolder.getFiles();
  while (files.hasNext()){
    file               = files.next();
    let ssnameid       = [];
    ssnameid.push(file.getName(),file.getId());
    list.push(ssnameid);
  }

  console.log(list);

  for (let g=0,len=list.length;g<len;g++) { 
   let id           = list[g][1]; // list of Spreadsheet names[0] and ID numbers[1]
   let csv          = DriveApp.getFileById(id);
   let docName      = csv.getName();
   let ss           = SpreadsheetApp.openById(id);
   let sheet        = ss.getSheetByName(docName+'.csv');                       
   /** save files as CSV to Google Drive folder */    
   let requestData  = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
   let sheetID      = sheet.getSheetId().toString();
   let url          = "https://docs.google.com/spreadsheets/d/"+id+"/export?gid="+sheetID+"&format=csv"
   let result       = UrlFetchApp.fetch(url, requestData);  
   let resource     = {
    title: docName+'.csv',
    mimeType: 'application/vnd.csv',
    parents: [{ id: 'downloadFolderID' }]
    }
   Drive.Files.insert(resource,result)
  }
}

Note that for this to work you need to add Drive API (Services > Add a Service > Drive API)

Answer 1

To download a sheet as csv whitout any further manipulation, try this auto-download script

gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('M E N U')
    .addItem('auto download', 'autoDownload')
    .addToUi();
}
function autoDownload() {
  var html = HtmlService.createHtmlOutputFromFile('download');
  SpreadsheetApp.getUi().showModalDialog(html, 'CSV download interface');
}
function saveAsCSV() {
  var ssid = 'your spreadsheet id';
  var folderID = 'temporary folder id'
  var csv = "";
  var ss = SpreadsheetApp.openById(ssid)
  ss.getSheets()[0].getDataRange().getValues().forEach(function (r) {
    csv += r.join(",") + "\n";
  });
  var url = DriveApp.getFolderById(folderID)
    .createFile(ss.getName() + '.csv', csv, MimeType.CSV)
    .getDownloadUrl()
    .replace("?e=download&gd=true", "");
  return url;
}

download.html

<!DOCTYPE html>
<html>
<body>
  Auto Download CSV ... please wait
</body>
<script>
  function executeDownload(url) {
      window.location.href = url;
  }
  window.onload=function() {
    google.script.run
      .withSuccessHandler(executeDownload)
      .saveAsCSV();
    window.setTimeout(function(){google.script.host.close()},9000);
  }
</script>
</html>

tested with chrome

you can modify ssid, temporary folder id and setTimeout parameter to optimize