Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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);
}
}
@kboulier74

This comment has been minimized.

Show comment
Hide comment
@kboulier74

kboulier74 Mar 30, 2016

So I'm using this script to create csv files for graph data variables for Adobe Illustrator. My problem is that my first column of data is a number and needs to wrapped in double quotes like "2016", otherwise Illustrator renders 2016 as a data value in the chart.

When I manually download sheet to CSV, I do get the quotes, But when I run the script they are stripped out.

In order to get the quotes to appear in the manual download I had to do it by formula " =CONCATENATE(CHAR(34),"2015",CHAR(34))"

Please help, if I can get this to work it will cut out a ton of production time for me.

So I'm using this script to create csv files for graph data variables for Adobe Illustrator. My problem is that my first column of data is a number and needs to wrapped in double quotes like "2016", otherwise Illustrator renders 2016 as a data value in the chart.

When I manually download sheet to CSV, I do get the quotes, But when I run the script they are stripped out.

In order to get the quotes to appear in the manual download I had to do it by formula " =CONCATENATE(CHAR(34),"2015",CHAR(34))"

Please help, if I can get this to work it will cut out a ton of production time for me.

@sts70nl

This comment has been minimized.

Show comment
Hide comment
@sts70nl

sts70nl Jun 6, 2016

Put // before rows 42 and 44

sts70nl commented Jun 6, 2016

Put // before rows 42 and 44

@brianjdaigle

This comment has been minimized.

Show comment
Hide comment
@brianjdaigle

brianjdaigle Aug 3, 2016

Thank you. Just what I needed

Thank you. Just what I needed

@cgrouge

This comment has been minimized.

Show comment
Hide comment
@cgrouge

cgrouge 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?

cgrouge 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

This comment has been minimized.

Show comment
Hide comment
@timoyuen

timoyuen Sep 1, 2016

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

timoyuen commented Sep 1, 2016

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

@jononovo

This comment has been minimized.

Show comment
Hide comment
@jononovo

jononovo 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

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

This comment has been minimized.

Show comment
Hide comment
@pg081292

pg081292 Nov 3, 2016

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

pg081292 commented Nov 3, 2016

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

@leonardonormando

This comment has been minimized.

Show comment
Hide comment
@leonardonormando

leonardonormando Mar 30, 2017

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);

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

This comment has been minimized.

Show comment
Hide comment
@Sharma-Ravin

Sharma-Ravin Apr 21, 2017

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.

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

This comment has been minimized.

Show comment
Hide comment
@Shivite

Shivite 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);
});
}

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

This comment has been minimized.

Show comment
Hide comment
@Sharma-Ravin

Sharma-Ravin Apr 28, 2017

hi does it work with drive api.

hi does it work with drive api.

@yakimi

This comment has been minimized.

Show comment
Hide comment
@yakimi

yakimi Aug 30, 2017

awesome! thanks!

yakimi commented Aug 30, 2017

awesome! thanks!

@alexavenger

This comment has been minimized.

Show comment
Hide comment
@alexavenger

alexavenger Sep 19, 2017

¿How to export only specific range from specific Sheet?

¿How to export only specific range from specific Sheet?

@martinneuschl

This comment has been minimized.

Show comment
Hide comment
@martinneuschl

martinneuschl Sep 23, 2017

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

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

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