前端之家

  1. 首页
  2. 问答

.getValue（）在Google脚本中花费的时间太长

• 问答

我有一些需要很长时间才能获得价值的单元。我经常会发现getvalues()快得多，只有几十毫秒。这是单元格的问题吗，或者功能到底有多慢？这种缓慢的原因是什么？我的脚本中还有更多getvalue()调用，但是它们都不会花费超过400毫秒的时间。很多次我跑步时，仅这两个单元格很难获得价值。

//Original issue
[19-11-11 19:05:16:980 ICT] Spreadsheetapp.Sheet.getRange([A13]) [0 seconds]
[19-11-11 19:05:19:286 ICT] Spreadsheetapp.Range.getvalue() [2.306 seconds]
[19-11-11 19:05:12:373 ICT] Spreadsheetapp.Sheet.getRange([F1]) [0 seconds]
[19-11-11 19:05:16:326 ICT] Spreadsheetapp.Range.getvalues() [3.952 seconds]

这是代码

//This function put data from a key sheet into a database sheet. 
function enterTransDatabase_()
{
var ss = Spreadsheetapp.getactiveSpreadsheet();
var sheetLocation = ss.getSheetByName('Defaultvalues');

//This get the location of data in the key sheet and database field and match them
var dBField = convert2SingleArray(sheetLocation.getRange('A26:A54').getvalues());
var keySummary = convert2SingleArray(sheetLocation.getRange('B26:B54').getvalues());
var keyDataLocation = convert2SingleArray(sheetLocation.getRange('C26:C54').getvalues());
var numberFormat = convert2SingleArray(sheetLocation.getRange('D26:D54').getvalues());

var sheetKey = ss.getSheetByName('Data_entry');
var sheetDB = ss.getSheetByName('Trans_DB_Temp');
var lastRow = sheetDB.getLastRow();
var transLines = Math.max(sheetKey.getRange('J10').getvalue(),1);
var nextSTT = ss.getSheetByName('Defaultvalues').getRange('G2').getvalue();
var beginTransRow; 
var maxSearchRow = 20;
var searchColumn = 7;
var formatArray = []
var dataRange = []


//Create numbering for data entries
var sttColumn = []
for (var i1 = 0; i1<transLines;i1++)
{
  sttColumn.push([nextSTT + i1])
}
sheetDB.getRange(lastRow+1,1,transLines,1).setvalues(sttColumn);

for (var iR=0; iR<transLines;iR++)
{
var copyArray = []
formatArray.push(numberFormat)
for (var iC =0; iC<dBField.length;iC++)
{
  if(keySummary[iC]==1)
  {
    if (keyDataLocation[iC] == 'D10')
    {copyArray[iC] = sheetKey.getRange(keyDataLocation[iC]).getDisplayValue();}
    else
    {copyArray[iC] = sheetKey.getRange(keyDataLocation[iC]).getvalue();}
  }
  else if (keySummary[iC]==0)
  {
    copyArray[iC] = sheetKey.getRange(14 + iR,keyDataLocation[iC]).getvalue();
  }
  else if (keySummary[iC]===2)     
  {
    if (dBField[iC]==='Time used')   
    {copyArray[iC] = Math.round((sheetKey.getRange('V14').getvalue() - sheetKey.getRange('V9').getvalue())/1000/transLines)}
    else if (dBField[iC]==='IsCurrent')   
    {copyArray[iC] = 1}
    else if (dBField[iC]==='STT dòng')   
    {copyArray[iC] = iR + 1}
    else if (dBField[iC]==='ID dòng')   
    {copyArray[iC] = (10000 + copyArray[26]*1)*1000 + copyArray[27]}
  }
}
dataRange.push(copyArray)
}
sheetDB.getRange(lastRow+1,2,dBField.length).setNumberFormats(formatArray)
sheetDB.getRange(lastRow+1,dBField.length).setvalues(dataRange)
sheetDB.getRange(lastRow+1,34,1).setvalue('Cell G6 in Default')
KeyUpdate()
return true;
}

这是执行日志。

[19-11-11 19:05:10:830 ICT] Spreadsheetapp.getactiveRange() [0 seconds]
[19-11-11 19:05:10:830 ICT] Spreadsheetapp.Range.getRow() [0 seconds]
[19-11-11 19:05:10:830 ICT] Spreadsheetapp.Range.getLastRow() [0 seconds]
[19-11-11 19:05:10:830 ICT] Spreadsheetapp.Range.getcolumn() [0 seconds]
[19-11-11 19:05:10:830 ICT] Spreadsheetapp.Range.getLastColumn() [0 seconds]
[19-11-11 19:05:10:830 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:10:832 ICT] Starting execution
[19-11-11 19:05:10:845 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:10:846 ICT] Spreadsheetapp.Spreadsheet.getactiveSheet() [0 seconds]
[19-11-11 19:05:10:903 ICT] Spreadsheetapp.Sheet.getName() [0.056 seconds]
[19-11-11 19:05:10:904 ICT] Spreadsheetapp.Spreadsheet.getactiveSheet() [0 seconds]
[19-11-11 19:05:10:904 ICT] Spreadsheetapp.Range.getcolumn() [0 seconds]
[19-11-11 19:05:10:904 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:904 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:904 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:905 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:10:906 ICT] Spreadsheetapp.Range.setvalue([false]) [0 seconds]
[19-11-11 19:05:10:907 ICT] Spreadsheetapp.Sheet.getRange([J10]) [0 seconds]
[19-11-11 19:05:11:193 ICT] Spreadsheetapp.Range.getvalue() [0.285 seconds]
[19-11-11 19:05:11:194 ICT] Spreadsheetapp.Sheet.getRange([D12]) [0 seconds]
[19-11-11 19:05:11:300 ICT] Spreadsheetapp.Range.getvalue() [0.105 seconds]
[19-11-11 19:05:11:302 ICT] Logger.log([Start checking,[]]) [0 seconds]
[19-11-11 19:05:11:303 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:11:303 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Data_entry]) [0 seconds]
[19-11-11 19:05:11:304 ICT] Spreadsheetapp.Sheet.getRange([J10]) [0 seconds]
[19-11-11 19:05:11:304 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:305 ICT] Logger.log([5.0,[]]) [0 seconds]
[19-11-11 19:05:11:305 ICT] Spreadsheetapp.Sheet.getRange([14,4]) [0 seconds]
[19-11-11 19:05:11:305 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:11:306 ICT] Spreadsheetapp.Sheet.getRange([14,5]) [0 seconds]
[19-11-11 19:05:11:306 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:11:307 ICT] Spreadsheetapp.Sheet.getRange([14,6]) [0 seconds]
[19-11-11 19:05:11:307 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:11:307 ICT] Spreadsheetapp.Sheet.getRange([14,7]) [0 seconds]
[19-11-11 19:05:11:308 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:11:308 ICT] Spreadsheetapp.Sheet.getRange([14,8]) [0 seconds]
[19-11-11 19:05:11:308 ICT] Spreadsheetapp.Range.geta1Notation() [0 seconds]
[19-11-11 19:05:11:309 ICT] Spreadsheetapp.Sheet.getRange([D7]) [0 seconds]
[19-11-11 19:05:11:441 ICT] Spreadsheetapp.Range.isBlank() [0.131 seconds]
[19-11-11 19:05:11:441 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:442 ICT] Spreadsheetapp.Sheet.getRange([D8]) [0 seconds]
[19-11-11 19:05:11:442 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:442 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:443 ICT] Spreadsheetapp.Sheet.getRange([D9]) [0 seconds]
[19-11-11 19:05:11:444 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:444 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:445 ICT] Spreadsheetapp.Sheet.getRange([D10]) [0 seconds]
[19-11-11 19:05:11:445 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:445 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:446 ICT] Spreadsheetapp.Sheet.getRange([D11]) [0 seconds]
[19-11-11 19:05:11:446 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:446 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:447 ICT] Spreadsheetapp.Sheet.getRange([D12]) [0 seconds]
[19-11-11 19:05:11:447 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:447 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:447 ICT] Spreadsheetapp.Sheet.getRange([J7]) [0 seconds]
[19-11-11 19:05:11:448 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:448 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:448 ICT] Spreadsheetapp.Sheet.getRange([D14]) [0 seconds]
[19-11-11 19:05:11:448 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:449 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:449 ICT] Spreadsheetapp.Sheet.getRange([E14]) [0 seconds]
[19-11-11 19:05:11:449 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:449 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:450 ICT] Spreadsheetapp.Sheet.getRange([F14]) [0 seconds]
[19-11-11 19:05:11:450 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:450 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:451 ICT] Spreadsheetapp.Sheet.getRange([G14]) [0 seconds]
[19-11-11 19:05:11:451 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:451 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:452 ICT] Spreadsheetapp.Sheet.getRange([H14]) [0 seconds]
[19-11-11 19:05:11:452 ICT] Spreadsheetapp.Range.isBlank() [0 seconds]
[19-11-11 19:05:11:452 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:452 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:11:453 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Defaultvalues]) [0 seconds]
[19-11-11 19:05:11:454 ICT] Spreadsheetapp.Sheet.getRange([A26:A54]) [0 seconds]
[19-11-11 19:05:11:596 ICT] Spreadsheetapp.Range.getvalues() [0.142 seconds]
[19-11-11 19:05:11:598 ICT] Spreadsheetapp.Sheet.getRange([B26:B54]) [0 seconds]
[19-11-11 19:05:11:598 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:11:599 ICT] Spreadsheetapp.Sheet.getRange([C26:C54]) [0 seconds]
[19-11-11 19:05:11:599 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:11:600 ICT] Spreadsheetapp.Sheet.getRange([D26:D54]) [0 seconds]
[19-11-11 19:05:11:601 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:11:602 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Data_entry]) [0 seconds]
[19-11-11 19:05:11:602 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Trans_DB_Temp]) [0 seconds]
[19-11-11 19:05:11:785 ICT] Spreadsheetapp.Sheet.getLastRow() [0.182 seconds]
[19-11-11 19:05:11:786 ICT] Spreadsheetapp.Sheet.getRange([J10]) [0 seconds]
[19-11-11 19:05:11:787 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:11:787 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Defaultvalues]) [0 seconds]
[19-11-11 19:05:11:788 ICT] Spreadsheetapp.Sheet.getRange([G2]) [0 seconds]
[19-11-11 19:05:11:906 ICT] Spreadsheetapp.Range.getvalue() [0.117 seconds]
[19-11-11 19:05:11:907 ICT] Spreadsheetapp.Sheet.getRange([1,7]) [0 seconds]
[19-11-11 19:05:12:089 ICT] Spreadsheetapp.Range.getvalue() [0.181 seconds]
[19-11-11 19:05:12:090 ICT] Spreadsheetapp.Sheet.getRange([2,7]) [0 seconds]
[19-11-11 19:05:12:090 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:091 ICT] Spreadsheetapp.Sheet.getRange([3,7]) [0 seconds]
[19-11-11 19:05:12:091 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:092 ICT] Spreadsheetapp.Sheet.getRange([4,7]) [0 seconds]
[19-11-11 19:05:12:092 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:092 ICT] Spreadsheetapp.Sheet.getRange([5,7]) [0 seconds]
[19-11-11 19:05:12:092 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:093 ICT] Spreadsheetapp.Sheet.getRange([6,7]) [0 seconds]
[19-11-11 19:05:12:093 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:094 ICT] Spreadsheetapp.Sheet.getRange([7,7]) [0 seconds]
[19-11-11 19:05:12:094 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:094 ICT] Spreadsheetapp.Sheet.getRange([8,7]) [0 seconds]
[19-11-11 19:05:12:094 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:095 ICT] Spreadsheetapp.Sheet.getRange([9,7]) [0 seconds]
[19-11-11 19:05:12:095 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:096 ICT] Spreadsheetapp.Sheet.getRange([10,7]) [0 seconds]
[19-11-11 19:05:12:096 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:096 ICT] Spreadsheetapp.Sheet.getRange([11,7]) [0 seconds]
[19-11-11 19:05:12:096 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:097 ICT] Spreadsheetapp.Sheet.getRange([12,7]) [0 seconds]
[19-11-11 19:05:12:097 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:098 ICT] Spreadsheetapp.Sheet.getRange([13,7]) [0 seconds]
[19-11-11 19:05:12:098 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:12:098 ICT] Spreadsheetapp.Range.getRow() [0 seconds]
[19-11-11 19:05:12:098 ICT] Spreadsheetapp.Sheet.getRange([750,1]) [0 seconds]
[19-11-11 19:05:12:372 ICT] Spreadsheetapp.Range.setvalues([[[750.0]]]) [0.273 seconds]
[19-11-11 19:05:12:373 ICT] Spreadsheetapp.Sheet.getRange([F1]) [0 seconds]
[19-11-11 19:05:16:326 ICT] Spreadsheetapp.Range.getvalues() [3.952 seconds]
[19-11-11 19:05:16:326 ICT] Spreadsheetapp.Sheet.getRange([D10]) [0 seconds]
[19-11-11 19:05:16:486 ICT] Spreadsheetapp.Range.getDisplayValue() [0.159 seconds]
[19-11-11 19:05:16:487 ICT] Spreadsheetapp.Sheet.getRange([J7]) [0 seconds]
[19-11-11 19:05:16:487 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:488 ICT] Spreadsheetapp.Sheet.getRange([D8]) [0 seconds]
[19-11-11 19:05:16:645 ICT] Spreadsheetapp.Range.getvalues() [0.157 seconds]
[19-11-11 19:05:16:647 ICT] Spreadsheetapp.Sheet.getRange([D7]) [0 seconds]
[19-11-11 19:05:16:782 ICT] Spreadsheetapp.Range.getvalues() [0.135 seconds]
[19-11-11 19:05:16:784 ICT] Spreadsheetapp.Sheet.getRange([D11]) [0 seconds]
[19-11-11 19:05:16:784 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:785 ICT] Spreadsheetapp.Sheet.getRange([D12]) [0 seconds]
[19-11-11 19:05:16:785 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:786 ICT] Spreadsheetapp.Sheet.getRange([D9]) [0 seconds]
[19-11-11 19:05:16:786 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:787 ICT] Spreadsheetapp.Sheet.getRange([A101]) [0 seconds]
[19-11-11 19:05:16:961 ICT] Spreadsheetapp.Range.getvalues() [0.173 seconds]
[19-11-11 19:05:16:962 ICT] Spreadsheetapp.Sheet.getRange([14,4]) [0 seconds]
[19-11-11 19:05:16:962 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:963 ICT] Spreadsheetapp.Sheet.getRange([14,12]) [0 seconds]
[19-11-11 19:05:16:963 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:964 ICT] Spreadsheetapp.Sheet.getRange([14,5]) [0 seconds]
[19-11-11 19:05:16:964 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:964 ICT] Spreadsheetapp.Sheet.getRange([14,6]) [0 seconds]
[19-11-11 19:05:16:964 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:965 ICT] Spreadsheetapp.Sheet.getRange([14,7]) [0 seconds]
[19-11-11 19:05:16:965 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:965 ICT] Spreadsheetapp.Sheet.getRange([14,9]) [0 seconds]
[19-11-11 19:05:16:966 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:966 ICT] Spreadsheetapp.Sheet.getRange([14,8]) [0 seconds]
[19-11-11 19:05:16:966 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:967 ICT] Spreadsheetapp.Sheet.getRange([A101]) [0 seconds]
[19-11-11 19:05:16:967 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:967 ICT] Spreadsheetapp.Sheet.getRange([14,10]) [0 seconds]
[19-11-11 19:05:16:968 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:968 ICT] Spreadsheetapp.Sheet.getRange([J11]) [0 seconds]
[19-11-11 19:05:16:968 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:969 ICT] Spreadsheetapp.Sheet.getRange([J12]) [0 seconds]
[19-11-11 19:05:16:969 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:969 ICT] Spreadsheetapp.Sheet.getRange([Q1]) [0 seconds]
[19-11-11 19:05:16:969 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:970 ICT] Spreadsheetapp.Sheet.getRange([V1]) [0 seconds]
[19-11-11 19:05:16:970 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:971 ICT] Spreadsheetapp.Sheet.getRange([V2]) [0 seconds]
[19-11-11 19:05:16:971 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:971 ICT] Spreadsheetapp.Sheet.getRange([V3]) [0 seconds]
[19-11-11 19:05:16:971 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:972 ICT] Spreadsheetapp.Sheet.getRange([V14]) [0 seconds]
[19-11-11 19:05:16:972 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:973 ICT] Spreadsheetapp.Sheet.getRange([V9]) [0 seconds]
[19-11-11 19:05:16:973 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:16:973 ICT] Spreadsheetapp.Sheet.getRange([J1]) [0 seconds]
[19-11-11 19:05:16:973 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:16:974 ICT] Spreadsheetapp.Sheet.getRange([750,29]) [0 seconds]
[19-11-11 19:05:16:975 ICT] Spreadsheetapp.Range.setNumberFormats([[[@,dd/mm/yyyy,@,#,##0,##0.00,#0%,dd/mm/yyyy hh:mm:ss,##0]]]) [0 seconds]
[19-11-11 19:05:16:975 ICT] Spreadsheetapp.Sheet.getRange([750,29]) [0 seconds]
[19-11-11 19:05:16:976 ICT] Spreadsheetapp.Range.setvalues([[[Gokids Việt Nam,15/8/19,HĐ VAT,Hoa Tươi- Quà Tặng& Trái Cây Yến Nhi,0314804996,03CB/18P,26230.0,Mua,Hoa tươi,bó,1.0,700000.0,K,910_IBS_new...) [0 seconds]
[19-11-11 19:05:16:977 ICT] Spreadsheetapp.Sheet.getRange([750,1]) [0 seconds]
[19-11-11 19:05:16:977 ICT] Spreadsheetapp.Range.setvalue([Cell G6 in Default]) [0 seconds]
[19-11-11 19:05:16:977 ICT] Spreadsheetapp.getactive() [0 seconds]
[19-11-11 19:05:16:978 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Data_entry]) [0 seconds]
[19-11-11 19:05:16:978 ICT] Spreadsheetapp.Sheet.getRange([V3]) [0 seconds]
[19-11-11 19:05:16:979 ICT] Spreadsheetapp.Range.setvalue([Mon Nov 11 19:05:16 GMT+07:00 2019]) [0 seconds]
[19-11-11 19:05:16:980 ICT] Spreadsheetapp.Sheet.getRange([A13]) [0 seconds]
[19-11-11 19:05:19:286 ICT] Spreadsheetapp.Range.getvalue() [2.306 seconds]
[19-11-11 19:05:19:287 ICT] Logger.log([Starting cleaning up sheet,[]]) [0 seconds]
[19-11-11 19:05:19:288 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:19:289 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Defaultvalues]) [0 seconds]
[19-11-11 19:05:19:289 ICT] Spreadsheetapp.Sheet.getRange([A2:A22]) [0 seconds]
[19-11-11 19:05:19:390 ICT] Spreadsheetapp.Range.getvalues() [0.1 seconds]
[19-11-11 19:05:19:391 ICT] Spreadsheetapp.Sheet.getRange([B2:B22]) [0 seconds]
[19-11-11 19:05:19:391 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:19:392 ICT] Spreadsheetapp.Sheet.getRange([C2:C22]) [0 seconds]
[19-11-11 19:05:19:393 ICT] Spreadsheetapp.Range.getvalues() [0 seconds]
[19-11-11 19:05:19:394 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:19:395 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Data_entry]) [0 seconds]
[19-11-11 19:05:19:395 ICT] Spreadsheetapp.Sheet.getRange([K1]) [0 seconds]
[19-11-11 19:05:19:522 ICT] Spreadsheetapp.Range.getvalue() [0.126 seconds]
[19-11-11 19:05:19:523 ICT] Spreadsheetapp.Sheet.getRange([V9]) [0 seconds]
[19-11-11 19:05:19:525 ICT] Spreadsheetapp.Sheet.getRange([V14]) [0 seconds]
[19-11-11 19:05:19:525 ICT] Spreadsheetapp.Range.getvalue() [0 seconds]
[19-11-11 19:05:19:526 ICT] Spreadsheetapp.Range.setvalue([Mon Nov 11 19:05:18 GMT+07:00 2019]) [0 seconds]
[19-11-11 19:05:19:526 ICT] Logger.log([started cleaning,[]]) [0 seconds]
[19-11-11 19:05:19:527 ICT] Spreadsheetapp.Sheet.getRange([D8:D11]) [0 seconds]
[19-11-11 19:05:19:528 ICT] Spreadsheetapp.Range.setNumberFormat([@]) [0 seconds]
[19-11-11 19:05:19:528 ICT] Spreadsheetapp.Sheet.getRange([L14:L100]) [0 seconds]
[19-11-11 19:05:19:720 ICT] Spreadsheetapp.Range.clear([{contentsonly=true}]) [0.191 seconds]
[19-11-11 19:05:19:721 ICT] Spreadsheetapp.Sheet.getRange([D1:E1]) [0 seconds]
[19-11-11 19:05:19:948 ICT] Spreadsheetapp.Range.clear([{contentsonly=true}]) [0.226 seconds]
[19-11-11 19:05:19:949 ICT] Spreadsheetapp.Sheet.getRange([A1]) [0 seconds]
[19-11-11 19:05:20:024 ICT] Spreadsheetapp.Range.setvalue([false]) [0.074 seconds]
[19-11-11 19:05:20:025 ICT] Spreadsheetapp.Sheet.getRange([D7]) [0 seconds]
[19-11-11 19:05:20:026 ICT] Spreadsheetapp.Range.setvalue([=iferror(VLOOKUP($E$1;$A$2:$B$6;2;0);"Phải điền")]) [0 seconds]
[19-11-11 19:05:20:026 ICT] Spreadsheetapp.Sheet.getRange([D8]) [0 seconds]
[19-11-11 19:05:20:027 ICT] Spreadsheetapp.Range.setvalue([=if(or(D7="Phải điền";NOT(ISFORMULA(D7)));"Phải điền";if(isnumber(E1);index(D2:D6;E1;1);IFERROR(vlookup(D7; MST_DB_All!D2:F;2;0); iferror(getaddrFromMST(D7;0);"Phải điền"))))]) [0 seconds]
[19-11-11 19:05:20:028 ICT] Spreadsheetapp.Sheet.getRange([D9]) [0 seconds]
[19-11-11 19:05:20:028 ICT] Spreadsheetapp.Range.setvalue([=IF(MID(Q1;6;1)="S";"Bán";"Mua")]) [0 seconds]
[19-11-11 19:05:20:029 ICT] Spreadsheetapp.Sheet.getRange([D10:D12]) [0 seconds]
[19-11-11 19:05:20:255 ICT] Spreadsheetapp.Range.clear([{contentsonly=true}]) [0.225 seconds]
[19-11-11 19:05:20:256 ICT] Spreadsheetapp.Sheet.getRange([I14]) [0 seconds]
[19-11-11 19:05:20:320 ICT] Spreadsheetapp.Range.setvalue([=if(or(D14="";$J$7="Tờ khai");"";G14*F14)]) [0.064 seconds]
[19-11-11 19:05:20:321 ICT] Spreadsheetapp.Sheet.getRange([J14]) [0 seconds]
[19-11-11 19:05:20:322 ICT] Spreadsheetapp.Range.setvalue([=if(D14="";"";(iferror(--H14;0)+1)*if($J$7="Tờ khai";G14;I14))]) [0 seconds]
[19-11-11 19:05:20:323 ICT] Spreadsheetapp.Sheet.getRange([J7]) [0 seconds]
[19-11-11 19:05:20:323 ICT] Spreadsheetapp.Range.setvalue([=VLOOKUP(mid(Q1;5;3);Defaultvalues!$M$1:$N$5;2;0)]) [0 seconds]
[19-11-11 19:05:20:324 ICT] Spreadsheetapp.Sheet.getRange([J8]) [0 seconds]
[19-11-11 19:05:20:325 ICT] Spreadsheetapp.Range.setvalue([false]) [0 seconds]
[19-11-11 19:05:20:325 ICT] Spreadsheetapp.Sheet.getRange([A14:H100]) [0 seconds]
[19-11-11 19:05:20:761 ICT] Spreadsheetapp.Range.clear([{contentsonly=true}]) [0.436 seconds]
[19-11-11 19:05:20:763 ICT] Spreadsheetapp.Sheet.getRange([I15:K100]) [0 seconds]
[19-11-11 19:05:20:914 ICT] Spreadsheetapp.Range.clear([{contentsonly=true}]) [0.151 seconds]
[19-11-11 19:05:20:915 ICT] Spreadsheetapp.Sheet.getRange([K14]) [0 seconds]
[19-11-11 19:05:20:979 ICT] Spreadsheetapp.Range.setvalue([false]) [0.064 seconds]
[19-11-11 19:05:20:980 ICT] Spreadsheetapp.Sheet.getRange([D14]) [0 seconds]
[19-11-11 19:05:20:981 ICT] Spreadsheetapp.Range.setvalue([=if(and(left($D$2;5)="Không";$J$8<>"");"Phải điền";if(isblank(C14);""; INDEX($D$2:$D$6;C14;1)))]) [0 seconds]
[19-11-11 19:05:20:981 ICT] Spreadsheetapp.Sheet.getRange([V10]) [0 seconds]
[19-11-11 19:05:20:982 ICT] Spreadsheetapp.Sheet.getRange([A14]) [0 seconds]
[19-11-11 19:05:20:982 ICT] Spreadsheetapp.Range.copyTo([Range,PASTE_FORMULA,false]) [0 seconds]
[19-11-11 19:05:20:983 ICT] Spreadsheetapp.Sheet.getRange([J1]) [0 seconds]
[19-11-11 19:05:21:347 ICT] Spreadsheetapp.Range.getvalue() [0.363 seconds]
[19-11-11 19:05:21:348 ICT] Spreadsheetapp.Sheet.getRange([J1]) [0 seconds]
[19-11-11 19:05:21:349 ICT] Spreadsheetapp.Range.setvalue([377.0]) [0 seconds]
[19-11-11 19:05:21:350 ICT] Spreadsheetapp.Sheet.getRange([F1]) [0 seconds]
[19-11-11 19:05:21:350 ICT] Spreadsheetapp.Range.setvalue([=vlookup(VLOOKUP($J$1;File_list!$A$2:$F;2;0);ClientDB!$C$2:$D;2;0)]) [0 seconds]
[19-11-11 19:05:21:351 ICT] Spreadsheetapp.Sheet.getRange([B2]) [0 seconds]
[19-11-11 19:05:21:351 ICT] Spreadsheetapp.Range.setvalue([=if(V12="";"";if(V11="SP"; iferror(query(Goods_DB_All!A2:D;" Select B where lower(D) contains '"&$V$12&"' and B contains '"&$F$1&"' limit 5");"Không có kết quả");iferror(query(MST_DB_All!A2:I;" Selec...) [0 seconds]
[19-11-11 19:05:21:352 ICT] Spreadsheetapp.Sheet.getRange([D2]) [0 seconds]
[19-11-11 19:05:21:352 ICT] Spreadsheetapp.Range.setvalue([=if(V12="";"";if(V11="SP"; iferror(query(Goods_DB_All!A2:D;" Select C where lower(D) contains '"&$V$12&"' and B contains '"&$F$1&"' limit 5");"Không có kết quả trong cơ sở dữ liệu,hãy nhập mới");ife...) [0 seconds]
[19-11-11 19:05:21:353 ICT] Spreadsheetapp.Sheet.getRange([17,100,11]) [0 seconds]
[19-11-11 19:05:21:354 ICT] Spreadsheetapp.Range.setBackground([null]) [0 seconds]
[19-11-11 19:05:21:354 ICT] Spreadsheetapp.Sheet.getRange([B7:J12]) [0 seconds]
[19-11-11 19:05:21:355 ICT] Spreadsheetapp.Range.setBorder([false,false,false]) [0 seconds]
[19-11-11 19:05:21:355 ICT] Spreadsheetapp.Sheet.getRange([B14:L100]) [0 seconds]
[19-11-11 19:05:21:356 ICT] Spreadsheetapp.Range.setBorder([false,false]) [0 seconds]
[19-11-11 19:05:21:356 ICT] Spreadsheetapp.Sheet.getRange([D14:D100]) [0 seconds]
[19-11-11 19:05:26:086 ICT] Spreadsheetapp.Range.getvalues() [4.729 seconds]
[19-11-11 19:05:26:087 ICT] Spreadsheetapp.Sheet.getRange([B14:L16]) [0 seconds]
[19-11-11 19:05:26:089 ICT] Spreadsheetapp.Range.setBorder([true,true,false]) [0 seconds]
[19-11-11 19:05:26:089 ICT] Spreadsheetapp.Sheet.getRange([B7:J12]) [0 seconds]
[19-11-11 19:05:26:090 ICT] Spreadsheetapp.Range.setBorder([true,false]) [0 seconds]
[19-11-11 19:05:26:091 ICT] Logger.log([Done resetting cells,[]]) [0 seconds]
[19-11-11 19:05:26:091 ICT] Spreadsheetapp.getactiveSpreadsheet() [0 seconds]
[19-11-11 19:05:26:092 ICT] Spreadsheetapp.Spreadsheet.getSheetByName([Data_entry]) [0 seconds]
[19-11-11 19:05:26:093 ICT] Spreadsheetapp.Sheet.getRange([V1]) [0 seconds]
[19-11-11 19:05:26:407 ICT] Spreadsheetapp.Range.getvalue() [0.313 seconds]
[19-11-11 19:05:26:559 ICT] Spreadsheetapp.Sheet.getImages() [0.152 seconds]
[19-11-11 19:05:31:400 ICT] Spreadsheetapp.OverGridImage.replace([https://drive.google.com/a/tsas.vn/uc?id=1knpfbX7k7TSDi2hQZ4lTheLBJ641Xvce&export=download]) [4.84 seconds]
[19-11-11 19:05:31:401 ICT] Logger.log([Replacing done,[]]) [0 seconds]
[19-11-11 19:05:33:020 ICT] Spreadsheetapp.OverGridImage.getHeight() [0.118 seconds]
[19-11-11 19:05:33:021 ICT] Spreadsheetapp.OverGridImage.getWidth() [0 seconds]
[19-11-11 19:05:33:022 ICT] Spreadsheetapp.Sheet.getRange([O1]) [0 seconds]
[19-11-11 19:05:33:112 ICT] Spreadsheetapp.Range.getvalue() [0.089 seconds]
[19-11-11 19:05:33:113 ICT] Spreadsheetapp.OverGridImage.setWidth([600]) [0 seconds]
[19-11-11 19:05:33:490 ICT] Spreadsheetapp.OverGridImage.setHeight([849]) [0.377 seconds]
[19-11-11 19:05:33:491 ICT] Logger.log([resize done,[]]) [0 seconds]
[19-11-11 19:05:33:492 ICT] Spreadsheetapp.Sheet.getRange([1,4]) [0 seconds]
[19-11-11 19:05:33:492 ICT] Spreadsheetapp.Range.activate() [0 seconds]
[19-11-11 19:05:33:493 ICT] Logger.log([Clean sheet done,[]]) [0 seconds]
[19-11-11 19:05:33:920 ICT] Execution succeeded [22.649 seconds total runtime]
cupid_llh 回答：.getValue（）在Google脚本中花费的时间太长

经过多次试验，我发现了速度缓慢的原因。事实证明，只要您将工作表从setValue切换到getValue的另一工作表，都将花费很长时间。根据我的实验，只有setValue构成“当前设置表”，只有getValue构成“切换表”。 getRange，getSheetByName，isBlank（需要重新检查），getLastRow等其他功能的效果不同。工作表是否已加载到内存中都没有关系。函数是getValue还是getValues都没关系。其他动作似乎无关紧要。例如

var sheetA = ss.getSheetByName('A');
var sheetB = ss.getSheetByName('B');
var cell1 = sheetA.getRange(1,1)
cell1.setValue('ABC')//"current sheet" is now sheet A
var something = sheetA.getRange(1,1).getValue()//Same sheet as last setValue=>Fast
[most other operations doesn't involve getValue in another sheet] //Fast
var cell2 = sheetB.getRange(1,1)//getRange from other sheet is not affected =>fast
cell1.setValue('CDE')//setValue is not "switching sheet" => fast 
cell1.setValue('GHK')//"current sheet" is now sheet A
var value2 = cell2.getValue() //<= getValue in sheet B. Slow,at least 2-3000 milliseconds.
  • 因此，如果可能并且您关心反应时间的秒数，那么在setValue和getValue时应尽量减少切换时间
  • 此外，如果您在一次操作中处理3张纸，则可以确保该操作至少需要4-6秒钟。
,

反复观察到Apps Script API会大大降低脚本的运行速度，如您所见。您应该设计脚本以最大程度地减少API调用次数。

best practices documentation特别指出了这一点：

  

替换读写命令很慢。为了加速脚本，   使用一个命令将所有数据读入数组，执行任何操作   对数组中的数据进行处理，然后用一个命令将数据写出。

文档中提供的示例描述了将性能提高70倍的情况。您的代码遇到与示例相同的问题（即在嵌套循环中执行API调用）。

这是脚本开头的一个简单而明显的示例：

var dBField = convert2SingleArray(sheetLocation.getRange('A26:A54').getValues());
var keySummary = convert2SingleArray(sheetLocation.getRange('B26:B54').getValues());
var keyDataLocation = convert2SingleArray(sheetLocation.getRange('C26:C54').getValues());
var numberFormat = convert2SingleArray(sheetLocation.getRange('D26:D54').getValues());

与其重复调用getRange()来捕获相邻的数据集，还可以通过单个getRange()调用来处理：

var sheetLocationValues = sheetLocation.getRange('A26:D54').getValues();
var transposedSheetLocationValues = [];
for (var i = 0; i < sheetLocationValues[0].length; i++) {
    transposedSheetLocationValues[i] = [];
    for (var j = 0; j < sheetLocationValues.length; j++) {
        transposedSheetLocationValues[i][j] = sheetLocationValues[j][i]
    }
}
var dbField = transposedSheetLocationValues[0];
var keySummary = transposedSheetLocationValues[1];
var keyDataLocation = transposedSheetLocationValues[2];
var numberFormat = transposedSheetLocationValues[4];

您的执行记录描述了getValue()和getRange()调用的大量重复。

在进入循环之前，您应该提取感兴趣的全部数据。然后在不进行API调用的情况下处理内存中的数据。完成后，使用尽可能少的API调用写入结果数据。

functiongoogle-apps-script
本文链接：https://www.f2er.com/3120372.html

大家都在问