Google spreadsheet: Download all the sheets at once

Question

When working with google spreadsheet, how to download all the sheets at once?

I want to use the option:

Comma-separated values

But it only download the current sheet, how to get them all?

Dreadful

@pnuts one .csv per sheet – Dreadful

Answer 1

After clicking download > pdf, select export > worksheet (instead of current sheet which is the default)

Fishworm

Why this is the accepted solution? The OP asked for CSV the answers is related to PDF – Bordereau

This should not be the accepted solution – Tetrad

Answer 2

For anyone who navigates to this question, trying to download all the tabs in their Google spreadsheets as CSV files at once, even in 2021, there does not seem to be a GUI button to do this. At least I could not see anything. The answer by @Amit Agarwal does well, to get all sheets, but if your file has comma-delimited data in cells, then data could get mangled.

I took Amit's approach https://stackoverflow.com/a/28711961 and combined it with Michael Derazon and Aaron Davis's approach here https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1 to dump all the tabs of a chosen Google spreadsheet into a folder in Google Drive. You can then just download the folder with a single click.

The following is Google script, not exactly a Javascript, and you would have to copy-paste this in https://script.google.com/ login with your Google id, and then create a project and then create a script app, and save and execute this.

// https://stackoverflow.com/a/28711961
function export_sheets_as_csv_to_folder() {
  // Sheet id is in URL https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit#gid=IGNORE
  var ss = SpreadsheetApp.openById('YOUR_SHEET_ID');
  var sheets = ss.getSheets();
  if (sheets === undefined || sheets.length === 0) {
    return;
  }
  var passThroughFolder = DriveApp.createFolder('YOUR_PREFERRED_FOLDER_NAME_IN_DRIVE');
  for (var s in sheets) {
    var csv = convertRangeToCsvFile_(sheets[s])
    passThroughFolder.createFile(sheets[s].getName() + ".csv", csv);
  }
}

// https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1
function convertRangeToCsvFile_(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);
  }
}

Photodisintegration

This works beautifully. It hung the first time but then I realized I forgot to edit the script and replace YOUR_SHEET_ID & YOUR_PREFERRED_FOLDER_NAME_IN_DRIVE – Mcgrew

I just used this script and can verify it still works in July 2022. Thanks! – Superable

This works well but is quite slow for large spreadsheets. Is it possible to write one line at a time instead of making a gigantic string? – Migdaliamigeon

This works in April 2024. Agreed with the need for speed up @jackdaw. – Chopfallen

Answer 3

You can use Google Scripts to save all the sheets of a spreadsheet into separate files.

function myFunction() {
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheets = ss.getSheets();
  for (var s in sheets) {
    var csv = "";
    var data = sheets[s].getDataRange().getValues();
    for (d in data) {
      csv += data[d].join(",") + "\n";
    }
    DriveApp.createFile(sheets[s].getName() + ".csv", csv);
  }
}

Jacintha

Answer 4

You can download all sheets/tabs from a Google sheet at once by using "File -> Download -> Microsoft Excel (.xlsx)" or "File -> Download -> OpenDocument (ODS)"

The excel file can be converted by the tool xlsx2csv into csv files. It runs in python or as executable, see: https://github.com/dilshod/xlsx2csv I use this and it works well.

The open office file can by converted with a command parameter into csv file: https://bugs.documentfoundation.org/show_bug.cgi?id=135762 There is also a macro which does this within the editor: https://www.briankoponen.com/libreoffice-export-sheets-csv/

I noticed a little difference between xlsx2csv and libre office 7.5: A cell with the content 123456789 is exported as 123456789.00000 in the csv with xlsx2csv, but as 123456789 with Libre Office.

I have experienced yesterday that an important script on Google site just stopped working because Google changed something. To be future proof I recommend going with OpenOffice and the command line conversion.

Irritative

Answer 5

the answer from @Soham works amazingly but it doesn't handle multiline values. It would be an easy fix just to add more checks to character \n along with , but I took the liberty to rewrite the function using map (and string.includes) so it is more concise.

function convertRangeToCsvFile_(sheet) {
  return sheet.getDataRange().getValues()
    .map(row => row.map(value => value.toString())
                   .map(value => (value.includes("\n") || value.includes(",")) ? "\"" + value + "\"" : value)
                   .join(','))
    .join('\n')
}

Flapdoodle

Answer 6

A slight variation on this that uses a zip instead of a folder to contain the sheets and does some modernizing of the great work done by keychera and Soham's answer.

You can use this as a bound script and it will add a menu item to the extensions menu:

// Code.gs
function exportSheetsToDrive() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  if (sheets === undefined || sheets.length === 0) {
    return;
  }
  const now = new Date();
  
  const csvBlobs = sheets.map((sheet) => {
    const name = sheet.getName();
    const csv = convertSheetToCsv(sheet);
    Logger.log({ name, length: csv.length });
    return Utilities.newBlob(csv, MimeType.CSV, `${name}.csv`)
  });

  const zipName = `export_${ss.getName()}_${now.toISOString()}.zip`;
  const zip = Utilities.zip(csvBlobs, zipName);
  DriveApp.createFile(zip);
}

function convertSheetToCsv(sheet) {
  return sheet
    .getDataRange()
    .getValues()
    .map((row) =>
      row
        .map((value) => value.toString())
        .map((value) =>
          value.includes("\n") || value.includes(",")
            ? '"' + value + '"'
            : value
        )
        .join(",")
    )
    .join("\n");
}

and

// Menu.gs
function onOpen(e) {
  const menu = SpreadsheetApp.getUi().createAddonMenu();

  menu
    .addItem('Export all sheets as CSV to Drive', 'exportSheetsToDrive')
    .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

Fertile

Answer 7

The answer from Amit Agarwal worked for me, and seems to be the most simple and effective to me. I tried to improve it by considering the solution from keychera.

The code is below:

function downloadSheetsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var targetFolder = DriveApp.createFolder('Your CSV Sheets Folder');
    Logger.log("Total sheet amount: " + sheets.length);
    for (var i = 0; i < sheets.length; i++) {   
        var data = sheets[i].getDataRange().getValues().map(row => row.map(value => value.toString())
               .map(value => (value.includes("\n") || value.includes(",")) ? "\"" + value + "\"" : value)
               .join(','))
               .join('\n')
        var sheetName = sheets[i].getName();
        targetFolder.createFile(sheetName + ".csv", data);
        Logger.log("Sheet Nº " + i + ": " + sheetName+ " converted to CSV.");
    }
}

Womanly