Skip to content

Instantly share code, notes, and snippets.

@mderazon
Last active April 2, 2024 22:25
Show Gist options
  • Save mderazon/9655893 to your computer and use it in GitHub Desktop.
Save mderazon/9655893 to your computer and use it in GitHub Desktop.
Google apps script to export to individual csv files all sheets in an open spreadsheet
/*
* script to export data in all sheets in the current spreadsheet as individual csv files
* files will be named according to the name of the sheet
* author: Michael Derazon
*/
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}];
ss.addMenu("csv", csvMenuEntries);
};
function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
// create a folder from the name of the spreadsheet
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
for (var i = 0 ; i < sheets.length ; i++) {
var sheet = sheets[i];
// 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
folder.createFile(fileName, csvFile);
}
Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}
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);
}
}
@brianjdaigle
Copy link

Thank you. Just what I needed

Copy link

ghost commented Aug 22, 2016

Instead of creating a new folder / new file every time, could I use this to update an existing file in the same folder?

@timoyuen
Copy link

timoyuen commented Sep 1, 2016

might i know how to install it in my current spreadsheet?

@jononovo
Copy link

jononovo commented Oct 3, 2016

Hi All,
How do I modify this to run every 24 hours and export to the same folder, but only a particular sheet (file), not all ActiveSheets?
Thanks,
Jon

@pg081292
Copy link

pg081292 commented Nov 3, 2016

Hi All ,
How i export date field without converting it in string.

@leonardonormando
Copy link

Got this script to save the csv file in the same folder by adding this line:
var folderX = DriveApp.getFolderById('dir_id');
Where you have to change the input the desired folder ID.

And, lastly,modifying this one:
folder.createFile(fileName, csvFile);
To
folderX.createFile(fileName, csvFile);

@Sharma-Ravin
Copy link

Hi I am using drive api and only able to access to first sheet of workbook. Presently I am having the sheet id. Please tell where to pass the file id to download multiple sheets of a workbook in a folder.

@Shivite
Copy link

Shivite commented Apr 26, 2017

How can we use this code to export all sheets of a google spreadsheet by using drive/sheet api. By using the below mentioned code I can only download the first sheet from spreadsheet and I want to download all the sheets available inside my spreadsheet. My code is given below.

function downloadGDriveFile (file) {
var request1 = gapi.client.drive.files.export({
'fileId': '1VLFgD8CNvXTVdHKyBdZPUmgFmhWMzg7qWLbxzLUTtSo',
//The id to second sheet is gid=156482034',
'mimeType': 'text/csv'
})
request1.then(function(response) {
console.log(response);
alert(response.body);
$.ajax({
type: "POST",
url: 'http://pathToFile/scripts/ravin/last/interfacefile.php',
data: {data : response.body},
success: function(response) {
}
});
//-----------------
}, function(err) {
console.log('Error');
console.log(err.result.error);
});
}

@Sharma-Ravin
Copy link

hi does it work with drive api.

@yakimi
Copy link

yakimi commented Aug 30, 2017

awesome! thanks!

Copy link

ghost commented Sep 19, 2017

¿How to export only specific range from specific Sheet?

@martinneuschl
Copy link

Hello Michael,

thank you for the script - it works perfectly! I was wondering, if it is possible to alter it somehow so it would be able to export files in .tsv format instead of .csv? I tried to replace the comma character on lines 203 and 211 with space, but I need tabs instead of spaces. Do you know about any solution, please?

Thanks,
Martin

@mroy-seedbox
Copy link

@martinneuschl: Just use "\t" instead of ","

@mroy-seedbox
Copy link

mroy-seedbox commented Jan 6, 2021

Instead of creating a new folder / new file every time, could I use this to update an existing file in the same folder?

@cgrouge: 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');
  }
}

@EdusanSanta
Copy link

Hi, this is being very helpful, but is there a way to DOWNLOAD the CSVs instead of creating a folder in Drive?

I need them to upload the files in another place, so having to download them from gdrive is adding 1 more step

@mroy-seedbox
Copy link

@EdusanSanta: You can do that directly from the Google Sheets UI:

File > Download > Comma-separated values (.csv, current sheet)

@EdusanSanta
Copy link

@EdusanSanta: You can do that directly from the Google Sheets UI:

File > Download > Comma-separated values (.csv, current sheet)

Yes, but i have many sheets and that option only exports the active sheet.

The script Will convert all My sheets into csv, but would be great if after that it downloads them.

Is it possible?

@mroy-seedbox
Copy link

mroy-seedbox commented Mar 10, 2021

@EdusanSanta: There's a way, but it's not as straightforward as you might like:

  • Browsers require the user to perform an action (i.e. click) to initiate a download - you can't "push" a download to the user. App Scripts don't have any special way to achieve this either.
  • If you want to download multiple files (csv files cannot contain multiple sheets/tabs), this means one click per file.
  • Downloads are usually done through the use of download links (MDN doc here - see "download" attribute), using either data urls, object urls, or traditional links pointing to a file on a server.
  • Data urls can have size limitations in some browsers, so use with caution. The data also needs to be encoded - either url encoded, or base64 encoded.
  • In general, object urls are preferred - but I don't think the API is available in app scripts.

Here are some resources if you'd like to learn more:

Here's how I would implement your download of all sheets:

  • Use app scripts to build a custom popup/dialog with a downloadable link for each sheet.
  • I would hijack the functionality already offered by Google Docs, and build links that point to the File > Download > CSV url for each sheet.
  • Here's what the urls looks like: https://docs.google.com/spreadsheets/d/[DOCUMENT_ID]/export?format=csv&id=[DOCUMENT_ID]&gid=[SHEET_ID]
  • You can get the DOCUMENT_ID via SpreadsheetApp.getActiveSpreadsheet().getId()
  • You can get the SHEET_ID for each sheet via Sheet.getSheetId()

@isaacmunuhe
Copy link

@mderazon thanks a lot for sharing this.
Could you be having a snippet code that can convert an individual sheet to a downloadable excel(xlsx) file.
I have tried the following code but the issue is, its downloading all sheets instead of an individual sheet.
function makeXlsx() { var sheetId = "1x53K43fytf55k4D0WqWckKCpX_1w0098-a8HM"; var url = "https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken(); var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); folder.createFile(blob); }

@arniearm
Copy link

Hi
I'm using this code to save a whole lot of bank account details to a CSV file, but some bank accounts start with a zero and when the account number is saved to the csv file, the leading zero is dropped. Please can someone suggest a edit that will solve this.

Thanks

@missinglina
Copy link

Hello - this is working but when I open the file in a text editor, there are many blank rows with:
"","","","","","","","","","",""
Is there a way to get it to stop at the end of the last row with data instead?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment