將所有作業表的靜態Excel匯出腳本切換為僅一張作業表

我有一個現有的應用程式腳本，它在我的 google 作業表中獲取許多作業表/選項卡（其中包括很多公式），然后將沒有公式的靜態資料匯出到輔助作業表（'12345foobarstaticdatasheet'）。在此之后，它將其保存為檔案夾（'12345foobarfolder'）中的 .xlsx excel 檔案輸出。

我想簡單地將這個腳本從獲取所有作業表更改為僅按名稱匯出一個（'My Foo Bar Sheet'）。當我嘗試這個時，我遇到了 for 回圈（TypeError: st1.getName is not a function）的錯誤。

這是為整個檔案（包括所有作業表）運行該程序的腳本：

function SaveMySheet() {
  var folder = DriveApp.getFolderById('12345foobarfolder');
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = source.getSheets();
  var target = SpreadsheetApp.openById('12345foobarstaticdatasheet');
  for(i in sheets)
  {
    var st1 = sheets[i];
    var st1Name = st1.getName();
    var st1data = st1.getDataRange().getValues();
    var st2 = target.getSheetByName(st1Name);
    if(st2 != null)
    {
      var rng = st2.getDataRange()
      if(rng != null && rng != undefined)
        rng.clearContent();
      st2.getRange(1,1,st1data.length,st1data[0].length).setValues(st1data);
    }
  }
  var blob = getGoogleSpreadsheetAsExcel('12345foobarstaticdatasheet',"My Report Filename " Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd hh:mm a'))
  folder.createFile(blob);
}

function getGoogleSpreadsheetAsExcel(ssID,name){
  try {

    var ss = SpreadsheetApp.getActive();

    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key="   ssID   "&exportFormat=xlsx";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer "   ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();

    blob.setName(name   ".xlsx");

   return blob;

  } catch (f) {
    Logger.log(f.toString());
  }
}

uj5u.com熱心網友回復：

這應該做。洗掉回圈并獲取特定的作業表source.getSheetByName("The name that is in the variable")

function SaveMySheet() {
  var st1Name = 'TheName';
  var folder = DriveApp.getFolderById('12345foobarfolder');
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById('12345foobarstaticdatasheet');
  var st1 = source.getSheetByName(st1Name)
  var st1data = st1.getDataRange().getValues();
  var st2 = target.getSheetByName(st1Name);
  if (st2 != null) {
    var rng = st2.getDataRange()
    if (rng != null && rng != undefined)
      rng.clearContent();
    st2.getRange(1, 1, st1data.length, st1data[0].length).setValues(st1data);
  }
  var blob = getGoogleSpreadsheetAsExcel('12345foobarstaticdatasheet', "My Report Filename "   Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd hh:mm a'))
  folder.createFile(blob);
}

function getGoogleSpreadsheetAsExcel(ssID, name) {
  try {
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key="   ssID   "&exportFormat=xlsx";
    var params = {
      method: "get",
      headers: { "Authorization": "Bearer "   ScriptApp.getOAuthToken() },
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(name   ".xlsx");
    return blob;

  } catch (f) {
    Logger.log(f.toString());
  }
}

標籤：for循环 谷歌应用脚​​本 谷歌表格

上一篇：如何使用Apps腳本使用來自不同Google表格選項卡的A列資料填充Google表單問題？

下一篇：如何使用filter()將其保留為2D陣列？