代码运行速度太慢 [英] Code runs too slow
问题描述
我试图运行一个代码来复制一个电子表格中的值并将它们复制到另一个,但是顺序并不相同(很难将其作为一个数组)。在某些情况下,它还会打印未知,并且在某些情况下还会格式化一些单元格。然而,它需要很长时间才能完成。有没有办法改进它?
function move(){
var sss = SpreadsheetApp.openById('xx');
var sourceSheet = sss.getSheetByName('CJ_Products');
var destinationSheet = sss.getSheetByName('Product2');
var lastRow = sourceSheet.getRange(sourceSheet.getLastRow(),1,1,1).getRow()
var i = 1
(while)(i <= lastRow){
var rowInt = destinationSheet.getRange(destinationSheet.getLastRow()+ 1,4,1,1).getRow()//获得行号
destinationSheet.getRange ('A'+ rowInt).setFormula('= Month(D'+ rowInt +')')
destinationSheet.getRange('B'+ rowInt).setFormula('= Weekday(D'+ rowInt +')' )
destinationSheet.getRange('C'+ rowInt).setFormula('= Day(D'+ rowInt +')')
destinationSheet.getRange('D'+ rowInt).setValue(sourceSheet.getRange ('A'+ i).getValues())//从源代码移动到目标
destinationSheet.getRange('E'+ rowInt +':F'+ rowInt).setValue('Unknown')//设置未知
destinationSheet.getRange('H'+ rowInt +':J'+ rowInt).setValue('Unknown')
destinationSheet.getRange('J'+ rowInt).setValue('CJ')
destinationSheet.getRange('K'+ rowInt ).setValue(sourceSheet.getRange('B'+ i).getValues())
destinationSheet.getRange('L'+ rowInt).setValue(sourceSheet.getRange('E'+ i).getValues() )
destinationSheet.getRange('M'+ rowInt).setValue(sourceSheet.getRange('F'+ i).getValues())
destinationSheet.getRange('N'+ rowInt).setValue( sourceSheet.getRange('J'+ i).getValues())
destinationSheet.getRange('S'+ rowInt).setValue(sourceSheet.getRange('G'+ i).getValues())
destinationSheet.getRange('T'+ rowInt).setValue(sourceSheet.getRange('H'+ i).getValues())
destinationSheet.getRange('O'+ rowInt).setFormula('= S' + rowInt +'* GOOGLEFINANCE(currency:EURUSD)')
destinationSheet.getRange('P'+ rowInt).setFormula('= T'+ rowInt +'* GOOGLEFINANCE(currency:EURUSD)')
destinationSheet.getRange('O'+ rowInt +':Q'+ rowInt)。b $ b destinationSheet.getRange('Q'+ rowInt).setFormula('= P'+ rowInt +'/ T'+ rowInt)
destinationSheet.getRange setNumberForm at('0.00 $')
i = i + 1
}
}
代码应该进行优化: 在循环中
getValue
和 setValue
代替更快的函数 getValues
, setValues
而不是集中你的循环做一次调用:
$ b $ p rowInt = destinationSheet.getRange(destinationSheet.getLastRow()+ 1,4 ,1,1).getRow()
尝试找出如何找到循环外的第一行,然后增加此值:
var rowStart = destinationSheet.getRange(destinationSheet.getLastRow()+ 1,4,1,1).getRow();
for(var row = rowStart; row< = lastRow,row ++)
{
//一些代码...
}
使用数组然后将数组中的值复制到范围中:
var公式= [];
for(var row = rowStart; row< = lastRow,row ++)
{
//一些代码...
formulas.push(['=月(D'+行+')']);
}
var rangeToPateFormulas = destinationSheet.getRange('A'+ rowStart +':A'+ lastRow);
rangeToPateFormulas.setFormulas(公式);
等等。查看更多信息:
https://developers.google.com/apps-script/reference/spreadsheet/range
https://developers.google.com/apps-script/guides/support/best-practices
I'm trying to run a code that copies values from one spreadsheet and copies them to another, however the order is not the same(hard to make it an array). In some cases it also prints 'Unknown' and in some it also formats some cells. However it makes way to much time to finish. Is there a way to improve it?
function move() {
var sss = SpreadsheetApp.openById('xx');
var sourceSheet = sss.getSheetByName('CJ_Products');
var destinationSheet = sss.getSheetByName('Product2');
var lastRow = sourceSheet.getRange(sourceSheet.getLastRow(), 1,1,1).getRow()
var i = 1
while(i<=lastRow){
var rowInt = destinationSheet.getRange(destinationSheet.getLastRow()+1, 4,1,1).getRow() //get row number
destinationSheet.getRange('A' + rowInt).setFormula('=Month(D'+rowInt+')')
destinationSheet.getRange('B' + rowInt).setFormula('=Weekday(D'+rowInt+')')
destinationSheet.getRange('C' + rowInt).setFormula('=Day(D'+rowInt+')')
destinationSheet.getRange('D' + rowInt).setValue(sourceSheet.getRange('A'+i).getValues()) //move from the source to destination
destinationSheet.getRange('E' + rowInt+':F'+rowInt).setValue('Unknown') //set to Unknown
destinationSheet.getRange('H' + rowInt+':J'+rowInt).setValue('Unknown')
destinationSheet.getRange('J' + rowInt).setValue('CJ')
destinationSheet.getRange('K' + rowInt).setValue(sourceSheet.getRange('B' +i).getValues())
destinationSheet.getRange('L' + rowInt).setValue(sourceSheet.getRange('E' +i).getValues())
destinationSheet.getRange('M' + rowInt).setValue(sourceSheet.getRange('F' +i).getValues())
destinationSheet.getRange('N' + rowInt).setValue(sourceSheet.getRange('J' +i).getValues())
destinationSheet.getRange('S' + rowInt).setValue(sourceSheet.getRange('G' +i).getValues())
destinationSheet.getRange('T' + rowInt).setValue(sourceSheet.getRange('H' +i).getValues())
destinationSheet.getRange('O' + rowInt).setFormula('=S'+rowInt+'*GOOGLEFINANCE("currency:EURUSD")')
destinationSheet.getRange('P' + rowInt).setFormula('=T'+rowInt+'*GOOGLEFINANCE("currency:EURUSD")')
destinationSheet.getRange('Q' + rowInt).setFormula('=P'+rowInt+'/T'+rowInt)
destinationSheet.getRange('O' + rowInt+':Q'+rowInt).setNumberFormat('0.00$')
i = i+1
}
}
The code should be optimised:
- You do all calculations in a loop
- You use
getValue
andsetValue
instead of faster functionsgetValues
,setValues
Instead of this concentrate your loop to do a single call:
var rowInt = destinationSheet.getRange(destinationSheet.getLastRow()+1, 4,1,1).getRow()
try to figure out how to find the first row outside the loop and then increment this value:
var rowStart = destinationSheet.getRange(destinationSheet.getLastRow()+1, 4,1,1).getRow();
for (var row = rowStart; row <= lastRow, row++)
{
// some code...
}
Use arrays and then copy the value from arrays into ranges:
var formulas = [];
for (var row = rowStart; row <= lastRow, row++)
{
// some code...
formulas.push(['=Month(D'+ row + ')']);
}
var rangeToPateFormulas = destinationSheet.getRange('A' + rowStart + ':A' + lastRow);
rangeToPateFormulas.setFormulas(formulas);
And so on. See more info:
https://developers.google.com/apps-script/reference/spreadsheet/range
https://developers.google.com/apps-script/guides/support/best-practices
这篇关于代码运行速度太慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!