加速 Google App 脚本/Javascript 和递归

我正在 Google App Script 中为 Google Sheet 编写一组脚本。我创建了一个用户界面来为 Google Sheet 提供网页体验，充当论坛。我以递归模式将讨论存储在电子表格中，如下所示：

  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

因此，嵌套的层数可以是不确定的。当页面加载时，它调用一个脚本，传入一个ID，该ID始终是上层节点的根ID（其中Parent_ID始终为0）。然后我返回一个节点，其中包含所有嵌套的子节点。然后，我需要使用嵌套组中每条记录的 ID 来执行一些计算。我的代码可以工作，但可能非常慢。迭代超过 20 条记录可能需要 15 秒以上的时间。任何人都可以提供有关如何加快速度并使代码更高效的反馈吗？这是我的代码：

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

功能recursePosts正在重新加载并重新初始化多个变量，例如ss, sheet and rows在内部函数中多次。您应该使这些变量在父作用域中保持不变，并仅调用这些方法一次

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*/
}

如果您想要变量的模块化加载，您可以使用这里描述了延迟加载技术 https://stackoverflow.com/questions/65150194/how-to-use-global-variables-while-avoiding-permissio

/**
 * 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);
}