问循环遍历数据并根据单元格值设置IndexMatch公式-由于.getValue()太慢

EN

function setFormulas() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); //gets the workbook being used
  var sheet = ss.getSheetByName("Open Requests"); //gets the active worksheet


  var sheetnametoWatch ="Open Requests"; //defines sheet 

  var columnnumbertoWatch = 14
  var valuetoWatch = ""
  var CostCenter = "'Open Requests'!"
  var lastrow = sheet.getLastRow();

  var datarange = sheet.getRange(11,2,lastrow -1,50).getValues()
  var row = 10

  for (i=0;i<datarange.length;i++){
    var rowID = 10 + i + 1

    if (datarange[i][0] == "CC"){
      var addedCell = '=CONCATENATE(F'+rowID+',N'+rowID+',K'+rowID+')';
      sheet.getRange(row + i+1,35).setFormula(addedCell);

      var MatchedCell = '=INDEX(Input!$Q:$Q,MATCH('+CostCenter+'$AI$'+rowID+',Input!$K:$K,0))';
      sheet.getRange(row + i+1,17).setFormula(MatchedCell); 

      sheet.getRange(row + i+1,18).setValue("kristin.j@ni.com")
    }
    else if (datarange[i][0] == "CC + TM1"){
      var addedCell = '=CONCATENATE(F'+rowID+',N'+rowID+',K'+rowID+')';
      sheet.getRange(row + i+1,35).setFormula(addedCell);

      var MatchedCell = '=INDEX(Input!$Q:$Q,MATCH('+CostCenter+'$AI$'+rowID+',Input!$K:$K,0))';
      sheet.getRange(row + i+1,17).setFormula(MatchedCell); 

      sheet.getRange(row + i+1,18).setValue("kristin.j@ni.com")
    }
    else if (datarange[i][0] == "GC"){
      sheet.getRange(row + i+1,18).setValue("kristin.j@ni.com")
    }

  }

 }