javascriptgoogle-apps-script

Function calling from another file to a function is working , but calling the same one as a global variable is not working


I tried to shorten the basic codes of gs.

function quantityAnalysis (){
  var subscription = magicSheet("dataRaw","Subscription");

  var idCol = msCol(subscription,"Subscription Id");
  var xlCol = msCol(subscription,"XL");
  var goCol = msGoCol(idCol,xlCol);
  var lastRow = msLastRow(subscription);

  var dataToFilter = subscription
  .getRange(1,idCol,lastRow,goCol)
  .getValues();
}

**In the second file **

function magicSheet (ssName,sheet="full"){
  //return sheet
    let id = sMagicSearch("id",ssName);
    if(sheet=="full"){
      return SpreadsheetApp.openById(id);
    }else{
      return SpreadsheetApp.openById(id).getSheetByName(sheet);
    }
  //end return sheet
}

It was working till I did this.

var subscription = magicSheet("dataRaw","Subscription");

function quantityAnalysis (){
  var idCol = msCol(subscription,"Subscription Id");
  var xlCol = msCol(subscription,"XL");
  var goCol = msGoCol(idCol,xlCol);
}

Error: magicSheet is not defined.

one more thing to tell :
subscription function is used in one more function to print values to the sheet.


Solution

  • Issue:

    Global statements (e.g. declaring global variables, calling functions outside any function declaration) are executed before the function that you decided to execute. They also seem to be executed in order, one script file after the other.

    In this case, var subscription = magicSheet("dataRaw","Subscription");, being in the first file, is executed before magicSheet is declared (in the second file). Therefore, it is not finding this function.

    Solution:

    Either wrap your global statement (calling magicSheet) inside a function, as you were doing, or with something like:

    function getSubscription() {
      return magicSheet("dataRaw","Subscription");
    }
    

    Or change the order of your files, moving the second one on top of the first one (in the new IDE, click the vertical three points for the second file and click Move file up.

    In any case, I don't think it's good practice to have global statements that reference objects from other files, so I'd just wrap that statement inside a function.