Skip to content

Instantly share code, notes, and snippets.

@franklinbaldo
Forked from mderazon/export-to-csv.gs
Last active April 26, 2020 16:14
Show Gist options
  • Save franklinbaldo/e3b788b1fad70000bdb649a256ffbbad to your computer and use it in GitHub Desktop.
Save franklinbaldo/e3b788b1fad70000bdb649a256ffbbad to your computer and use it in GitHub Desktop.
Google apps script to export to individual csv files all sheets in an open spreadsheet
function myFunction() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("orange");
var range = sheet.getRange("1:1").getValues();
var titles = range[0];
var a1range = "A1:A";
var a1array = [];
a1range = a1range +titles.length*titles.length;
var b1range = "B1:B";
var b1array = [];
var allarray = [];
var pearson = ss.getSheetByName("pearson");
for (var i=0;i<titles.length;i++){
for (var j=0;j<titles.length;j++){
var firsttitle = titles[i];
var secondtitle = titles[j];
a1array.push(firsttitle);
b1array.push(secondtitle);
allarray.push([firsttitle,secondtitle]);
}
}
pearson.getRange("A:B").setValues(allarray);
}
/*
* 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() {
saveAsCSV();
};
function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var file = DriveApp.getFileById(ss.getId());
var folders = file.getParents();
var sheets = ss.getSheets();
// get the folder of the spreadsheet
var folder = DriveApp.getFileById(ss.getId()).getParents().next();
var date = new Date();
date = date.toISOString();
for (var i = 0 ; i < sheets.length ; i++) {
var sheet = sheets[i];
// append ".csv" extension to the sheet name
var fileName = ss.getName() + "_" + date + "_"+ sheet.getName()+ ".csv";
// convert all available sheet data to csv format
var currentCsvFileName = ss.getName() + "_"+ sheet.getName()+ ".csv";
var currentCsvFile = folder.getFilesByName(currentCsvFileName);
Logger.log(currentCsvFile.hasNext());
var csvFile = downloasCsv(sheet,ss);
if (currentCsvFile.hasNext()){
currentCsvFile.next().setContent(csvFile);
}
else {
folder.createFile(currentCsvFileName, csvFile);
}
// 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 downloasCsv (sheet,ss){
var activeRange = sheet.getDataRange();
var sheetId = sheet.getSheetId();
var ssID = ss.getId();
var sheetUrl = "https://docs.google.com/spreadsheets/u/0/d/" + ssID + "/export?format=csv&gid=" + sheetId;
var csvFile = UrlFetchApp.fetch(sheetUrl);
return csvFile;
}
function saveAsCsv () {
var downloasCsv = function (sheet,ss){
var activeRange = sheet.getDataRange();
var sheetId = sheet.getSheetId();
var ssID = ss.getId();
var sheetUrl = "https://docs.google.com/spreadsheets/u/0/d/" + ssID + "/export?format=csv&gid=" + sheetId;
var csvFile = UrlFetchApp.fetch(sheetUrl);
return csvFile;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var file = DriveApp.getFileById(ss.getId());
var folders = file.getParents();
var sheets = ss.getSheets();
// get the folder of the spreadsheet
var folder = DriveApp.getFileById(ss.getId()).getParents().next();
var date = new Date();
date = date.toISOString();
for (var i = 0 ; i < sheets.length ; i++) {
var sheet = sheets[i];
// append ".csv" extension to the sheet name
var fileName = ss.getName() + "_" + date + "_"+ sheet.getName()+ ".csv";
// convert all available sheet data to csv format
var activeRange = sheet.getDataRange();
var sheetId = sheet.getSheetId();
var ssID = ss.getId();
var sheetUrl = "https://docs.google.com/spreadsheets/u/0/d/" + ssID + "/export?format=csv&gid=" + sheetId;
var csvFile = UrlFetchApp.fetch(sheetUrl);
return csvFile;
var csvFile = this.downloasCsv(sheet,ss);
// 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());
}
saveAsCsv();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment