Google电子表格:一次下载所有工作表 [英] Google spreadsheet: Download all the sheets at once

问题描述

使用Google电子表格时，如何一次下载所有工作表?

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

我要使用以下选项:

以逗号分隔的值

但是它仅下载当前工作表，如何将其全部获取?

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

推荐答案

对于导航到此问题的任何人，即使到2021年，都试图一次将CSV电子表格中的所有标签下载为CSV文件，是一个GUI按钮来做到这一点.至少我什么都看不到.@Amit Agarwal的答案很好，可以获取所有工作表，但是如果文件中的单元格中有逗号分隔的数据，则数据可能会被弄乱.

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.

我接受了阿米特(Amit)的方法 https://stackoverflow.com/a/28711961 ，并将其与迈克尔·德拉赞(Michael Derazon)和亚伦·戴维斯(Aaron Davis)的方法相结合此处 https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1 转储所有标签选定的Google电子表格到Google云端硬盘的文件夹中.然后，您只需单击一下即可下载该文件夹.

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.

以下是Google脚本，不完全是Java脚本，您必须将其复制粘贴到 https://script.google.com/使用您的Google ID登录，然后创建一个项目，然后创建一个脚本应用程序，并保存并执行此操作.

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);
  }
}

这篇关于Google电子表格:一次下载所有工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！