How To Download All Sheets Google Sheets

by

relying on Google Sheetsyou are relying on Google Sheets for storing your data, there might come a time when you need to download all these sheets for offline usage or backup. However, Google Sheets doesn’t provide a straightforward way for downloading all sheets at once. Fear not, because in this blog post, we will guide you on how to download all your sheets in Google Sheets.

Downloading Individual Sheets

If you only have a few sheets, it might not be too bothersome to download them one by one. Here’s how:

  • Open the Google Sheets file.
  • Click on the File menu.
  • Select Download, then choose your preferred file format (e.g., Microsoft Excel, OpenDocument Format, PDF Document, etc.).

The downside of this method is it’s time-consuming when you have dozens of sheets. If that’s the case, we need a more efficient method.

Using Google Apps Script to Download All Sheets

Google Apps Script is a powerful tool that allows you to automate tasks within Google’s Apps. By using it, we can create a script to download all sheets from a Google Sheets file at once. Let’s see how.

  1. Open your Google Sheets file.
  2. Click on Extensions -> Apps Script.
  3. In the Apps Script, delete any code in the script editor and replace it with the following code:
        function downloadAllSheets() {
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheets = ss.getSheets();
          // loop through all sheets
          for (var i = 0; i < sheets.length; i++) {
            // create a new spreadsheet for each sheet
            var tempSpreadsheet = SpreadsheetApp.create("TEMP SPREADSHEET " + sheets[i].getName());
            sheets[i].copyTo(tempSpreadsheet);
            // get the ID of the temporary spreadsheet
            var tempSpreadsheetId = tempSpreadsheet.getId();
            // export the temporary spreadsheet to xlsx
            var url = "https://docs.google.com/spreadsheets/d/" + tempSpreadsheetId + "/export?format=xlsx";
            var options = {
              method: "GET",
              headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
              muteHttpExceptions: true
            };
            var response = UrlFetchApp.fetch(url, options);
            DriveApp.createFile(response.getBlob()).setName(sheets[i].getName() + ".xlsx");
            // delete the temporary spreadsheet
            DriveApp.getFileById(tempSpreadsheetId).setTrashed(true);
          }
        }

Note: This script will download all sheets as separate .xlsx files.

  1. Click on the diskette icon or press CTRL + S to save the script. You may name it anything you like, for instance, “DownloadAllSheets”.
  2. Finally, click on the play button (▶) situated in the toolbar to run the script. It may ask for permissions, grant them. Within a few moments, you should have all the sheets downloaded as .xlsx files in your Google Drive.

And that’s it! You now know how to download all sheets from a Google Sheets file. Hope you found this guide helpful.