Hot to export Multiple sheets in (.csv) format in Google Sheets

Question

In above image you can see that I have alot of files created in one google sheet. Now what I want is to export all of these sheet files in seperated files in ".csv" format. If I click on exprt button so it will save file as main file but I want to make each spreeadsheet a seperated csv file.

Eg: California.csv Alaska.csv

Any help is appreciated.

Thanks

I try the default export method but thsi is not what I wnat.

Expecting to get all of my spreeadsheet in seperate .csv Files

Answer 1

You can do that with Apps Script, like this:

'use strict';

function test() {
  const ss = SpreadsheetApp.getActive();
  const timezone = ss.getSpreadsheetTimeZone();
  const prefix = Utilities.formatDate(new Date(), timezone, 'yyyy-MM-dd ');
  console.log(`Exporting files...`);
  const result = exportTabsAsCsvToDrive_(ss, /./i, prefix);
  console.log(`Wrote ${result.files.length} files in folder '${result.folder.getName()}' at ${result.folder.getUrl()}.`);
}

/**
* Exports sheets each into its own CSV file.
*
* @param {SpreadsheetApp.Spreadsheet} ss Optional. A spreadsheet with sheets to export. Defaults to the active spreadsheet.
* @param {RegExp} sheetNameRegex Optional. A regex to match to sheet names. Defaults to all sheets.
* @param {String} prefix Optional. A text string to prepend to filenames. Defaults to ''.
* @param {String} suffix Optional. A text string to append to filenames. Defaults to ''.
* @param {DriveApp.Folder} folder Optional. The folder where to save the files in. Defaults to the spreadsheet's folder.
* @return {Object} { folder, files[] }
*/
function exportTabsAsCsvToDrive_(ss = SpreadsheetApp.getActive(), sheetNameRegex = /./i, prefix = '', suffix = '', folder) {
  // version 1.1, written by --Hyde, 2 December 2022
  //  - see https://stackoverflow.com/a/74654152/13045193
  folder = folder || DriveApp.getFileById(ss.getId()).getParents().next();
  const files = [];
  ss.getSheets().forEach(sheet => {
    const sheetName = sheet.getName();
    if (!sheetName.match(sheetNameRegex)) return;
    const filename = prefix + sheetName + suffix + '.csv';
    const values = sheet.getDataRange().getDisplayValues();
    const csvData = textArrayToCsv_(values);
    files.push(DriveApp.createFile(filename, csvData, MimeType.CSV).moveTo(folder));
  });
  return { folder: folder, files: files };
}

/**
* Converts text to a CSV format.
*
* Usage: 
*   const range = SpreadsheetApp.getActiveRange();
*   const csvData = textArrayToCsv_(range.getDisplayValues());
*
* When the data looks like this:
*   header A1       header B1                   header C1
*   text A2         text with comma, in B2      text with "quotes" in C2
*
* ...the function will return this:
*   "header A1", "header B1", "header C1"
*   "text A2", "text with comma, in B2", "text with ""quotes"" in C2"
*
* Lines end in a newline character (ASCII 10).
*
* @see https://en.wikipedia.org/wiki/Comma-separated_values#General_functionality
* @param {String[][]} data The text to convert to CSV.
* @param {String} escapeDoublequote Optional. Use '\\"' to prefix double quotes in data as in \" instead of the default "".
* @return {String} The text converted to CSV.
*/
function textArrayToCsv_(data, escapeDoublequote = '""') {
  // version 1.1, written by --Hyde, 1 April 2024
  //  - see https://stackoverflow.com/a/72689533/13045193
  return (
    data.map(row => row.map(value => `"${String(value).replace(/"/g, escapeDoublequote)}"`))
      .map(row => row.join(', '))
      .join('\n')
  );
}