Speeding up Google App Script/Javascript and Recursion

Question

I am writing a set of scripts in Google App Script, for a Google Sheet. I created a user interface to provide a web page experience for the Google Sheet, acting as a discussion forum. I store discussions in a recursive pattern, in the spread sheet, like this:

  ID    |    Parent_ID  |   Title      |      Note                | Forum_ID   | Is_Active  

1       |   0           |  Some Title  |       Some Discussion    | 100        | True

2       |   0           |  Some Title  |       Some Discussion    | 100        | True

3       |   2           |  Some Title  |       Some Discussion    | 100        | True

4       |   3           |  Some Title  |       Some Discussion    | 100        | True

5       |   2           |  Some Title  |       Some Discussion    | 100        | True

So, the nesting can be of an indeterminate number of levels. When the page loads, it call a script, passing in an ID, which will always be the root ID of upper node (where the Parent_ID is always 0). I then return a node, with all nested children. I then need to use the ID of each record in the nested group, to perform a couple of calculations. The code I have works, but can be very slow. To iterate over 20 records can take upwards of 15 seconds. Can anyone provide feedback on how to speed this up and make the code more efficient? Here is my code:

function GetMessageBoardChildren(message_id) {
    console.time('Gettingcomments') //this block of code can take around 2-3 seconds to run through (where the number of records is about 50)
    var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
    var sheet = ss.getSheetByName(MESSAGE_BOARD);
    var rows = sheet.getDataRange().getValues();               
    var newArray = [];
    var CommentsArray = [];   
    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];     
        if (row[5] == 1) {        
             CommentsArray.push({
                post_id: row[0].toString(),
                parent_id: row[1].toString(),
                forum_id: row[2].toString(),
                title: htmlEscape(row[3].toString()),
                message: htmlEscape(row[4].toString()),
                is_active: row[5].toString(),
                date_created: ConvertUnixTimeStampToDateTime(row[6].toString()),
                created_by_id: row[8].toString()      
            })
        }    
    }   
    console.timeEnd('Gettingcomments') 
   
    console.time('BuildgingTree')// this is fast.  couple of miliseconds
    tree = nest( CommentsArray );  
    for(var j = 0; j<tree.length; j++){ 
      if(tree[j].post_id == message_id){
         newArray.push(tree[j])
      }
    }
   console.timeEnd('BuildgingTree')
   
   console.time('recursing') //this can take 11-15 seconds to complete
   var t = recursePosts(newArray);
   console.timeEnd('recursing')
   return t;  

}

function recursePosts(posts){      
  for(var i=0;i<posts.length;i++){
    if(posts[i].children){
      recursePosts(posts[i].children)
     } 
     console.time('GettingVotes')
     var voteCount = GetVotesByCommentId(posts[i].post_id); 
     posts[i].number_up_posts = voteCount.upVote,
     posts[i].number_down_posts = voteCount.downVote
     console.timeEnd('GettingVotes')
     console.time('GettingUsername')
     posts[i].created_by = GetUserNameByUserId( posts[i].created_by_id);
     console.timeEnd('GettingUsername')
     posts[i].created_by_id="";     
  }
  return posts;
}

const nest = (items, id = '0', link = 'parent_id') =>items
    .filter(item => (item[link] === id )  )
    .map(item => ({ ...item,children: nest(items, item.post_id) }));

function ConvertUnixTimeStampToDateTime(unix_timestamp) {
    var a = new Date(unix_timestamp * 1000);
    var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    var year = a.getFullYear();
    var month = months[a.getMonth()];
    var date = a.getDate();
    var hour = a.getHours();
    var min = a.getMinutes();
    var sec = a.getSeconds();
    var time = a.getMonth() + "/" + date + "/" + year + " " + hour + ":" + min + ":" + sec;   
    return time;

}
function htmlEscape(str){
//prevent injection
    return str.replace(/<[^>]+>/g, "")
}
function GetUserNameByUserId(ID){
   var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
   var sheet = ss.getSheetByName(USERS);
   var rows = sheet.getDataRange().getValues();
   var userName = "";
   
   for (var i = 1; i < rows.length; i++) {
     var row = rows[i];
     if(row[0] === ID){
       userName = row[3];
       break;
     }
   }
   if(userName == ""){
     userName = "Admin"
   }
   return userName;    
}

function GetVotesByCommentId(comment_id) {
    var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
    var sheet = ss.getSheetByName(COMMENT_VOTES);
    var rows = sheet.getDataRange().getValues();
    var countUp = 0;
    var countDown = 0;

    for (var i = 0; i < rows.length; i++) {
      var row = rows[i];
        if (row[1] === comment_id && row[2] === 1) {
            countUp++;
        }else if (row[1] === comment_id && row[2] === -1){
          countDown++;
        }        
    }
    return {upVote:countUp, downVote:countDown};
}

Answer 1

The function recursePosts is reloading and reinitializing multiple variables like ss, sheet and rows multiple times in the inner functions. You should make those variables constant in the parent scope and call those methods only once

const config = {
  ss: null,
  mb_sheet: null,
  users_sheet: null,
  comments_sheet: null,
  mb_rows: null,
  users_rows: null,
  comments_rows: null,
};

function initAll_() {
  config.ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  [config.mb_sheet, config.users_sheet, config.comments_sheet] = [
    MESSAGE_BOARD,
    USERS,
    COMMENT_VOTES,
  ].map(name => config.ss.getSheetByName(name));
  [config.mb_rows, config.users_rows, config.comments_rows] = [
    config.mb_sheet,
    config.users_sheet,
    config.comments_sheet,
  ].map(sheet => sheet.getDataRange().getValues());
}


function GetMessageBoardChildren(message_id) {
  /*Initialize everything once and only once*/initAll_();
  console.time('Gettingcomments'); 
  //Removed var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  // var sheet = ss.getSheetByName(MESSAGE_BOARD);
  var rows = /*Modified*/ config.mb_rows;
  /*stuff*/
}

function GetUserNameByUserId(ID) {
  // var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  // var sheet = ss.getSheetByName(USERS);
  var rows = config.users_rows
  /*stuff*/
}

function GetVotesByCommentId(comment_id) {
  // var ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  // var sheet = ss.getSheetByName(COMMENT_VOTES);
  var rows = config.comments_rows;
  /*stuff*/
}

---

If you want modular loading of variables, you can use the lazy loading technique described here

/**
 * A sheet configuration object containing class sheet and
 *   it's full datarange values
 * @typedef {Object} SheetConfig
 * @property {GoogleAppsScript.Spreadsheet.Sheet} sheet
 * @property {Object[][]} values
 */

/**
 * Global configuration object
 * @type {{
 * ss:GoogleAppsScript.Spreadsheet.Spreadsheet,
 * [x:string]:SheetConfig|GoogleAppsScript.Spreadsheet.Spreadsheet}}
 */
const config = {
  get ss() {
    delete this.ss;
    return (this.ss = SpreadsheetApp.getActive());
  },
};
(function addSheetGettersToConfig_() {
  /*Add 3 {SheetConfig}  to config object*/
  [MESSAGE_BOARD,USERS,COMMENT_VOTES,].forEach(name =>
    Object.defineProperty(config, name, {
      enumerable: true,
      configurable: true,
      get: function() {
        delete this[name];
        return (this[name] = {
          sheet: this.ss.getSheetByName(name),
          get values() {
            delete this.values;
            return (this.values = this.sheet.getDataRange().getValues());
          },
        });
      },
    })
  );
})();

console.log('config before');
console.log(config);

function test_lazyLoading() {
  console.log(config[MESSAGE_BOARD].values);
  console.log('config after');
  console.log(config);
}