[Backend] 既存スプレッドシートをTSVファイル形式でダウンロードする

[wakamenod]

[Backend] 既存スプレッドシートをTSVファイル形式でダウンロードする

[wakamenod]

ダウンロード用、GAS

// 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('xxxxxxxxxxxxxxxxxxxxxxxx');
  var sheets = ss.getSheets();
  if (sheets === undefined || sheets.length === 0) {
    return;
  }
  var passThroughFolder = DriveApp.createFolder('suito_data');
  for (var s in sheets) {
    Logger.log(sheets[s].getName());
    var csv = convertRangeToCsvFile_(sheets[s])
    Logger.log(csv);
    passThroughFolder.createFile(sheets[s].getName() + ".tsv", 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 the data is a date, convert it to a string in 'YYYY-MM-DD' format.
          if (data[row][col] instanceof Date) {
            var year = data[row][col].getFullYear();
            var month = ("0" + (data[row][col].getMonth() + 1)).slice(-2);
            var day = ("0" + data[row][col].getDate()).slice(-2);
            data[row][col] = `${year}-${month}-${day}`;
          }

          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) {
          csv += data[row].join("\t") + "\r\n";
        }
        else {
          csv += data[row].join("\t") ;
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}