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:
getValue
and setValue
instead of faster functions getValues
, 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
这篇关于代码运行太慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!