google-sheetsgoogle-apps-scriptgoogle-drive-api

Transform string to variable


I want to assign editor to spreadsheets, but there are error on getting spreadsheetID.

11:04:54 AM Error Exception: Unexpected error while getting the method or property getFileById on object DriveApp.

Here is an example of the code I'm using:

myfunction(){
    var spreadsheetID1 = '1IDofspreadsheet1';
    var spreadsheetID2 = '1IDofspreadsheet2';
    ...;

    var emailList1 = ['user1.1@blabla.com', 'user1.2@blabla.com', ...];
    var emailList2 = ['user2.1@blabla.com', 'user2.2@blabla.com', ...];
    ...;

    for (var i=1 ; i<=limit ; i++){
        var file = DriveApp.getFileById('spreadheetID'+i);
        'emailList'+i.forEach(function(email)){
            file.addEditor(email);
            logger.log(file+' updated')
        }
    }
}

Function getFilebyID is given value as string not as variable that defined above. is there way to transform that value to be dynamic variable so function getFilebyID get spreadsheet ID as defined not as string?


Solution

  • Although I'm not sure whether I could correctly understand your expected result, how about the following modifications?

    By the way, in your script, it seems that spreadheetID of DriveApp.getFileById('spreadheetID'+i); is different from spreadsheetID of var spreadsheetID1. Also, in the case of forEach(function(email)){, forEach(function(email){. And, forEach is not enclosed by (). Please be careful about this.

    Also, in the case of addEditor in the loop of 'emailList'+i.forEach(function(email)){}), addEditors(emailAddresses) of Class File can be used.

    Pattern 1:

    In this pattern, the shorthand property name is used. And, obj1 and obj2 are prepared for retrieving each value. So, please set obj1 and obj2 and set limit for your situation.

    In this case, 'spreadsheetID' + i and 'emailList' + i can retrieve the values.

    function myFunction() {
      var spreadsheetID1 = '1IDofspreadsheet1';
      var spreadsheetID2 = '1IDofspreadsheet2';
      ...;
    
      var emailList1 = ['user1.1@blabla.com', 'user1.2@blabla.com', ...];
      var emailList2 = ['user2.1@blabla.com', 'user2.2@blabla.com', ...];
      ...;
    
      // I modified the below script.
      var obj1 = { spreadsheetID1, spreadsheetID2,,, };
      var obj2 = { emailList1, emailList2,,, };
    
      for (var i = 1; i <= limit; i++) {
        var spreadheetID = obj1['spreadsheetID' + i];
        var emailList = obj2['emailList' + i];
        DriveApp.getFileById(spreadheetID).addEditors(emailList);
      }
    }
    

    Pattern 2:

    In this pattern, the destructuring assignment is used. And, array is prepared for retrieving each value. So, please set array for your situation. In this case, limit is not used. From your showing script, I guessed that in your situation, spreadsheetID1 and spreadsheetID2 might correspond to emailList1 and emailList1, respectively. So, I proposed this modification.

    function myFunction() {
      var spreadsheetID1 = '1IDofspreadsheet1';
      var spreadsheetID2 = '1IDofspreadsheet2';
      ...;
    
      var emailList1 = ['user1.1@blabla.com', 'user1.2@blabla.com', ...];
      var emailList2 = ['user2.1@blabla.com', 'user2.2@blabla.com', ...];
      ...;
    
      // I modified the below script.
      var array = [
        [spreadsheetID1, emailList1],
        [spreadsheetID2, emailList2],
        ,
        ,
        ,
      ];
    
      array.forEach(([spreadheetID, emailList]) =>
        DriveApp.getFileById(spreadheetID).addEditors(emailList)
      );
    }
    

    References: