function in google script

user_15035430 0
Viewed 141

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 ;

(function calling from another file to a function is working , but calling the same one as a global varible is not working ).

google-apps-script javascript
2 Answers

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.

user_106120110

The second file with function magicSheet (... should be above the Code.gs in the editor. You can move it by clicking 3 vertical dots icon in the editor when hovering filename.

(If anyone is using clasp istead of editor, use "filePushOrder" in .clasp.json as explained here: https://github.com/google/clasp/issues/72#issuecomment-427202206 )

user_63484310
Related