I have an existing app script that takes the many sheets/tabs in my google sheet (which includes a lot of formulas) and then exports the static data without formulas to a secondary sheet ('12345foobarstaticdatasheet'). After this, it saves that as an .xlsx excel file output in a folder ('12345foobarfolder').

I'd like to simply change this script from getting all the sheets to only exporting one by name ('My Foo Bar Sheet'). When I try this I'm getting errors with the for loop (TypeError: st1.getName is not a function).

Here's the script to run the process for the whole file including all sheets:

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

CodePudding user response：

This should do. Remove the loop and get the specific sheet with 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());
  }
}