Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apps Script extremely slow or endlessly "Preparing for execution..."

I have written a very simple code on my Google Sheets file. This is the purpose:

  1. Save some cells values from StaticSheet (all the Copyxxx) that need to be copied in DynamicSheet.
  2. Get the value of one specific cell inserted by the user manually.
  3. Enter a While loop useful only to increase an indicator and get the number of the row where I want to copy those values previously saved.
  4. Copy those values on this row but different columns.

The problem is that it seems that most of the time it does not even run the script after I told it to do so. What is funny is that sometimes it works, super slowly, but it works for like a couple of minutes. And after it stops working again.

Could you please tell me what am I missing here please?

function Copy_Static_on_Dynamic() {
  var app = SpreadsheetApp;
  var ss = app.openById("xxxxyy--------yyzzzz")
  var StaticSheet = ss.getSheetByName("DEAT Price");
  var DynamicSheet = ss.getSheetByName("DEAT Price + TEST");
  var CopySKU = StaticSheet.getRange(5,1,40);
  var CopyPrices = StaticSheet.getRange(5,3,40,4);
  var CopyUsage = StaticSheet.getRange(5,8,40);
  var Week_1 = StaticSheet.getRange(2,4).getValues();
  var i = 1;
  Logger.clear();
  while(DynamicSheet.getRange(i,3).getValues() != Week_1)
  {
   Logger.log(i);
    i+=1;
  }
  CopySKU.copyTo(DynamicSheet.getRange(i,4,40));
  CopyPrices.copyTo(DynamicSheet.getRange(i,6,40,4));
  CopyUsage.copyTo(DynamicSheet.getRange(i,11,40));
}
like image 321
Andrea Avatar asked Jan 02 '23 13:01

Andrea


2 Answers

If you see the "Preparing for Execution" message in the Apps Script editor, you can reload the browser window and run the function again. The program will likely go away.

like image 139
Amit Agarwal Avatar answered Jan 04 '23 02:01

Amit Agarwal


So I think I have solved it. As Serge insas was saying I had my script running on the background, I found it out on the "Execution" section, where you can also interrupt them. After I discover it I kept testing, and I saw that the while loop needed almost 2 seconds to check the condition every time, making the script incredibly long. So instead of:

while(DynamicSheet.getRange(i,3).getValues() != Week_1)

... I have created a variable declared previously such as:

var WeekLOOP = DynamicSheet.getRange(i,3).getValues();
while(WeekLOOP != Week_1) { --- }

... and now the script needs few milliseconds to run the condition. I don't have enough technical knowledge to say if this was the only issue, but is what apparently solved my problem. Thanks to all for the support! Will come back if I need any further help :)

like image 38
Andrea Avatar answered Jan 04 '23 03:01

Andrea