Google apps script to export an individual sheet as csv file

export-named-sheet-as-csv.gs

/*
 * script to export data of the named sheet as an individual csv files
 * sheet downloaded to Google Drive and then downloaded as a CSV file
 * file named according to the name of the sheet
 * original author: Michael Derazon (https://gist.github.com/mderazon/9655893)
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Download Primary Time File", functionName: "saveAsCSV"}];
  ss.addMenu("Creating a Timetable", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Primary Time');
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
  // append ".csv" extension to the sheet name
  fileName = sheet.getName() + ".csv";
  // convert all available sheet data to csv format
  var csvFile = convertRangeToCsvFile_(fileName, sheet);
  // create a file in the Docs List with the given name and the csv data
  var file = folder.createFile(fileName, csvFile);
  //File downlaod
  var downloadURL = file.getDownloadUrl().slice(0, -8);
  showurl(downloadURL);
  
}

function showurl(downloadURL) {
  var app = UiApp.createApplication().setHeight('60').setWidth('150');
  //Change what the popup says here
  app.setTitle("Your timetable CSV is ready!");
  var panel = app.createPopupPanel()
  //Change what the download button says here
  var link = app.createAnchor('Click here to download', downloadURL);
  panel.add(link);
  app.add(panel);
  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    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] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

After running, raises the following error:

Exception: UiApp has been deprecated. Please use HtmlService instead.Details

@gabrielgz92 I got it to work by replacing the showurl function with this:

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

@EdieLemoine Thanks!!

@gabrielgz92 I got it to work by replacing the showurl function with this:

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

You didn't change anything else?

Hi any way to get the csv file encrypted to UTF-8? Thanks!

Thanks for putting this together, I am trying to get this to work but some of the cells within my sheet have line breaks within them. Is there a way to get the convertRangeToCsvFile_ function to remove these line breaks? Right now it is creating a new line when it finds a line break within a cell.

@gabrielgz92 I got it to work by replacing the showurl function with this:

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

You didn't change anything else?

https://gist.github.com/Ryahn/8a2a9ef241731031c998a51eadabc9ec

This is an updated version that is working on V8 script runtime. But to answer your question specifically.
Replace

function showurl(downloadURL) {
  var app = UiApp.createApplication().setHeight('60').setWidth('150');
  //Change what the popup says here
  app.setTitle("Your timetable CSV is ready!");
  var panel = app.createPopupPanel()
  //Change what the download button says here
  var link = app.createAnchor('Click here to download', downloadURL);
  panel.add(link);
  app.add(panel);
  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

With

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}```

This CSV code works very well, I have an issue that I am trying to resolve with the exported Date formats. My Google sheet has date format as MM/DD/YYYY and the CSV files have date format { Thu Aug 27 2020 00:00:00 GMT-0400 (Eastern Daylight Time) }

Looking to see if there is a way to get this CSV download code to use a date format or MM/DD/YYY.

This CSV code works very well, I have an issue that I am trying to resolve with the exported Date formats. My Google sheet has date format as MM/DD/YYYY and the CSV files have date format { Thu Aug 27 2020 00:00:00 GMT-0400 (Eastern Daylight Time) }

Looking to see if there is a way to get this CSV download code to use a date format or MM/DD/YYY.

It's been a while since I worked on this, and I modified it quite a lot for my needs.
What you could try, instead of this:
activeRange.getValues()
Do this:
activeRange.getDisplayValues()

If I remember correctly it'll then use the formatted values you see in the spreadsheet.

thanks ,great job @mrkrndvs

@Matt-McDonald-AS: That's because the CSV formatting code above isn't correct (it only checks for commas) - but there's more things to take into account:

• Quotes themselves must be escaped (double them -> " becomes "")
• Commas must be escaped (put string in quotes)
• New lines must be escaped (put string in quotes)
• Basically, all the values should be quoted, just to be safe.

I've updated the function to the following, and it works great:

function convertRangeToCsvFile_(csvFileName, sheet) {
  try {
    var data = sheet.getDataRange().getDisplayValues();
    if (data.length > 1) {
      var rows = [];
      data.forEach(row => {
        var cols = [];
        row.forEach(col => {
          cols.push(`"${col.replace(/"/g, '""')}"`);
        });

        rows.push(cols.join(','));
      });
      
      return rows.join('\n');
    }
  } catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

Is it possible to check if the file already exists (was saved previously), then overwrite it? This way keeping the URL the same?

@Toordokter: Yes, you can. Just use this function:

function writeFile(folder, fileName, data) {
  var existingFiles = folder.getFilesByName(fileName);
  if (existingFiles.hasNext()) {
    existingFiles.next().setContent(data);
  } else {
    folder.createFile(fileName, data, 'text/csv');
  }
}

I couldn't get the link in the revised showurl function to work, but I found this SO post that was helpful. The revised line of code is,

var link = HtmlService.createHtmlOutput('<a rel="noreferrer noopener" href= "' + downloadURL + '" target="_blank">Click here to download</a>');

Love this thread!

Have added this to a Google Sheets based tool we're using.

And it works fine for me, but when I share the sheet a 304 error pops up when the user clicks on the link to download.

Has anyone else encountered this and has a solution?

@AlliePavs it's probably a permission problem (also because the code is 403 not 304, that is forbidden). When running a google apps script, the user should authorize it's google account to run script on his behalf.

Thanks @GabrieleCalarota - it's strange because the person has granted the script permission to run. The file is being saved to Google Drive, it's just the click to download the CSV which is coming up with the error. I'll run through all the page permissions and try again - thanks!

While using the convertRangeToCsvFile function I came across a problem when making it a data string if a comma was found. The problem was if the string already contained a ", it would then add another row. So I created a while loop removing all, then making it a string. There probably is an easy way with a regExp:
while(data[row][col].toString().indexOf('"') != -1){ data[row][col] = data[row][col].replace('"', '') }

@AlliePavs I'm also seeing the 403 issue you encountered. Did you find a solution?

Yes! A friend of mine cracked it.

It was something to do with default chrome security settings whereby if you have 2 or more accounts that you switch between, you're actually signed into both of them rather than just the one you're using.

There's a security feature that will check permissions on both accounts and if one of them doesn't have the permissions, it wont let you download the file.

So, by adding the following to the script it circumvents this:

//set permissions to public to avoid double account glitch
folder.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

It's important to note however, that the folders and files it creates in the users drive account, are downloadable by anyone with the link.

Hope this helps!

@Matt-McDonald-AS: That's because the CSV formatting code above isn't correct (it only checks for commas) - but there's more things to take into account:

• Quotes themselves must be escaped (double them -> " becomes "")
• Commas must be escaped (put string in quotes)
• New lines must be escaped (put string in quotes)
• Basically, all the values should be quoted, just to be safe.

I've updated the function to the following, and it works great:

function convertRangeToCsvFile_(csvFileName, sheet) {
  try {
    var data = sheet.getDataRange().getDisplayValues();
    if (data.length > 1) {
      var rows = [];
      data.forEach(row => {
        var cols = [];
        row.forEach(col => {
          cols.push(`"${col.replace(/"/g, '""')}"`);
        });

        rows.push(cols.join(','));
      });
      
      return rows.join('\n');
    }
  } catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

Hi, there is an error
TypeError: col.replace is not a function

@hiimkhaled: Hmmmm, looks like sometimes col isn't a string somehow. 🤔

Try with this: cols.push(`"${col.toString().replace(/"/g, '""')}"`);

Lines 53-72 could be simplified a little:

csvFile =  lapply(data, sapply, toString) %>% 
    as.data.frame() %>% 
    readr::format_csv()

@debruine: How do you make that work? I don't think Google Apps Script supports the R language.

Many thanks for @mrkrndvs for this valued share.
Many thanks too for @maedox for his response

Indeed, I got an error on the csv file generated.
In france the decimal is a comma.
But in the generated csv with getValues() function, the commas was remplaced by points.
Now with getDisplayValues() all is ok.

Thanks @mrkrndvs, this is great!

I'm having some problems with the function "onOpen"

Exception: Cannot call SpreadsheetApp.newMenu() from this context.

Can anyeone help me?

@Matt-McDonald-AS: That's because the CSV formatting code above isn't correct (it only checks for commas) - but there's more things to take into account:

• Quotes themselves must be escaped (double them -> " becomes "")
• Commas must be escaped (put string in quotes)
• New lines must be escaped (put string in quotes)
• Basically, all the values should be quoted, just to be safe.

I've updated the function to the following, and it works great:

function convertRangeToCsvFile_(csvFileName, sheet) {
  try {
    var data = sheet.getDataRange().getDisplayValues();
    if (data.length > 1) {
      var rows = [];
      data.forEach(row => {
        var cols = [];
        row.forEach(col => {
          cols.push(`"${col.replace(/"/g, '""')}"`);
        });

        rows.push(cols.join(','));
      });
      
      return rows.join('\n');
    }
  } catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

I'd rather recommend keeping the original code to keep the split functionality, but else a good improvement.

`function convertRangeToCsvFile_(sheet, rowsPerFile) {
// get available data range in the spreadsheet
var activeRange = sheet.getDataRange();
try {
var data = activeRange.getValues();
var csvFiles = [];

// loop through the data in the range and build a string with the csv data
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].replace(/"/g, '""')}"`) ; //  modification of original code
      }
    }

    // join each row's columns
    // add a carriage return to end of each row, except for the last one
    if (row < data.length-1) {
      csv += data[row].join(",") + "\r\n";
    }
    else {
      csv += data[row];
    }

    if ((row + 1) % rowsPerFile === 0) {
      csvFiles.push(csv);
      csv = "";
    }
  }
}
// If there are remainders that doesnt reach the number of files needed.
// It will add another file, containing all remainder rows there.
// 10010 rows / 20 files = 10 rows excess
// 21st file will contain those 10 rows excess
// No excess means no 21st file
if(csv) {
  csvFiles.push(csv);
}
return csvFiles;

}
catch(err) {
Logger.log(err);
}
}
`

This was great, thank you!

@gabrielgz92 I got it to work by replacing the showurl function with this:

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

This helped a TON. Thank you for this update! Worked perfect.

Hello, in my case I got a correct downloadURL but only working if I manually introduce it to the browser. However if url is called within the Apps Script (showModalDialog()) I got initial 303 status redirection followed up 18 302 status redirection since I end up with ERR_TOO_MANY_REDIRECTS. Any clue?

• Folder is created OK
• Csv file is created OK
• URL file is created OK
• By changing folder permissions is not working aswell.

Other approach I did--> If I just copy the GET request from manually get File > Download > CSV in the browser works but within the dialog got this error:

Thanks!