Answer the question
In order to leave comments, you need to log in
How to add a cycle?
Please add a loop for the script so that it goes through all the sheets and saves them to files
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();
var ssid = ss.getId();
var fileInDrive = DriveApp.getFileById(ssid);
var folderInDrive = fileInDrive.getParents().next().getId();
for (var i = 0 ; i < sheets.length ; i++) {
var sheet = sheets[i];
filename = ss.getName() + sheet.getName() + ".csv";
}
var csv = "";
var v = SpreadsheetApp
.getActiveSpreadsheet()
.getActiveSheet()
.getDataRange()
.getDisplayValues();
v.forEach(function(e) {
csv += e.join(";") + "\n";
});
var url = DriveApp.getFolderById(folderInDrive)
.createFile(filename, csv, MimeType.CSV)
.getDownloadUrl()
.replace("?e=download&gd=true","");
return url;
}
Answer the question
In order to leave comments, you need to log in
You need to add sheet data to the loop and call the file generation from the loop
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{ name: 'export as csv files', functionName: 'userActionSaveAllSheetsAsCSV' }];
ss.addMenu('csv', csvMenuEntries);
}
function userActionSaveAllSheetsAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var ssid = ss.getId();
var fileInDrive = DriveApp.getFileById(ssid);
var folderInDrive = fileInDrive.getParents().next().getId();
const urls = sheets.map((sheet) => {
const filename = `${sheet.getParent().getName()} ${sheet.getName()}.csv`;
const array = sheet.getDataRange().getValues();
const url = saveArrayToCSV_(array, filename, folderInDrive);
return { url, filename, folderInDrive };
});
console.log(JSON.stringify(urls, null, ' '));
}
/**
* @param {string[][]} array
* @param {string} filename
* @param {string} folderInDrive
* @returns {string} The file url
*/
function saveArrayToCSV_(array, filename, folderInDrive) {
const data = array.map((row) => row.join(';')).join('\n');
const url = DriveApp.getFolderById(folderInDrive)
.createFile(filename, data, 'text/csv')
.getDownloadUrl()
.replace('?e=download&gd=true', '');
return url;
}
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question