CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
tboooe
tboooe

Reputation: 11

Google Script - setValue

I am trying to learn Google Script. I want to write a script that uses a For Loop to save the value of each cell, clear the cell, then paste that saved value back into it. The data is in cells C6 - C17. So far, I am able to get my For Loop to work clearing each cell one by one with a 1sec pause in between but I cant figure out how to use setValue to copy the value back into the cell. If I change the last line to source.setValue("test") then my script works, clearing each cell then replacing it with "test".

Here is my code:

function forceRefresh() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Fidelity Daily");
    for (var i =6 ; i<=17;i++)
    {
        var source = sheet.getRange(i,3);
        var values = source.getValues();
        source.clear();
        Utilities.sleep(1000);
        source.setValue(values);
    }
}

Upvotes: 0

Views: 647

Answers (3)

Daniel
Daniel

Reputation: 1

The answer for your second question.

Utilities.sleep only runs in the beginning of each function only one time. I hope this helps.

And unfortunately there is no such command like delay(); or something, in google script.

Upvotes: 0

tboooe
tboooe

Reputation: 11

I was able to solve my issue by adding the command SpreadsheetApp.flush() after the source.clear() command.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201358

Values retrieved by getValues() is 2 dimensional array. In your script, values is [["value"]]. So for your script in your question, when you want to import values to a cell using setValue(), it is required to modify as follows.

From :

source.setValue(values);

To :

source.setValue(values[0][0]);

Note :

If you want to retrieve one value from one cell, you can use getValue(). In this case, value retrieved by getValue() can be used by source.setValue(value);.

References :

I don't know whether this is an answer for you. If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions