Google-Sheets - Break a loop based on cell value

Question

I am currently running a timer based script (once a week) that copies a couple of values from one range (source) to another (target).

The source range is the result of a query based on a import. Thus it goes through multiple calculation steps before displaying the proper result. Usually this should be an easy task - just add a sleep timer to be sure and be done with it. However, the script copies the data based on an unfinished calculation.

Here's how I approached the this:

I setup a cell that checks if the data was correctly copied (trigger cell)

Now, I could just run a timer on sunday every hour or so, checking if the trigger is true or false. But I was wondering if there is a way to do this with a loop.

I am fairly new to javascript so I'm not very confident with implementing possible solutions from the web. It seems that loops can only be broken based on values within the loop. A do/while (while 'trigger' == false) loop for example just loops for 5 Minutes until it times out. I think a function call with the function calling itself should do the trick, but I couldn't figure out how to do this properly. My version just seems to run once and break - even when I change the triggervalue manually to force a loop.

    function looper(){

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("example");

    var trigger = sheet.getRange("a1").getValue();
    var source = sheet.getRange("b1:j1").getValues();
    var target = sheet.getRange("b2:j2");  

    Utilities.sleep(5000)
    if (trigger == false){
    target.setValues(source);
    looper;
 }
}

Welcome. In order to correctly understand your question, can you provide a sample Spreadsheet including the input and output you want? — Tedinoz
– Tedinoz, Commented Jul 9, 2019 at 11:25
BTW. You said copies a couple of values from one range (source) to another (target). I just want to clarify something, if you are using "query based on a import" then you are NOT dealing with values per se, you are dealing the dynamic results of a formula. So you might want to consider a different command such as copyTo(destination, copyPasteType, transposed) where you can set an option for what you want to paste (and "transposed" is just an option, so you can turn it off). — Tedinoz
– Tedinoz, Commented Jul 9, 2019 at 11:25

TwitchBronBron · Accepted Answer · 2019-07-09 11:16:53Z

0

In JavaScript, to call a function you need to use parentheses. So for the last line, try doing this: looper(); instead.

answered Jul 9, 2019 at 11:16

TwitchBronBron

3,1863 gold badges25 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google-Sheets - Break a loop based on cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related