Google表格:导出到TSV [英] Google Sheets: Exporting to TSV

问题描述

我想创建一个将当前工作表自动导出到Google表格上的TSV文件的函数.是否可以使用Google Apps脚本导出到制表符分隔的值文件，例如CSV的convertRangeToCsvFile?

I would like to create a function that automatically exports the current sheet to a TSV file on Google Sheets. Is it possible to export to a tab separated values file using Google Apps Script, i.e. like convertRangeToCsvFile for CSV?

推荐答案

我找到了Michael Derazon的csv文件代码，并将其修改为tsv文件.希望对您有帮助.

I found this code for csv files by Michael Derazon and modified it for tsv files. I hope it helps.

/*
 * script to export data in all sheets in the current spreadsheet as   individual csv files
 * files will be named according to the name of the sheet
 * author: Michael Derazon
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tsvMenuEntries = [{name: "export as TSV files", functionName:   "saveAstsv"}];
  ss.addMenu("tsv", tsvMenuEntries);
};

function saveAstsv() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/   /g,'_') + '_tsv_' + new Date().getTime());
  for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".tsv" extension to the sheet name
    fileName = sheet.getName() + ".tsv";
    // convert all available sheet data to tsv format
    var tsvFile = convertRangeTotsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the tsv data
    folder.createFile(fileName, tsvFile);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}

function convertRangeTotsvFile_(tsvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var tsvFile = undefined;

    // loop through the data in the range and build a string with the tsv  data
    if (data.length > 1) {
      var tsv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf("\t") != -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) {
          tsv += data[row].join("\t") + "\r\n";
        }
        else {
        tsv += data[row].join("\t");
             }
      }
      tsvFile = tsv;
    }
    return tsvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

这篇关于Google表格:导出到TSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！