How to make a loop script for Google sheet?

user_9288740 0
Viewed 79

I wanted to have a script that will change text to a hyperlink using script. I have column D in Google sheet from D1:D, for example:

12346
34566
23456
23455... and so on...

Currently, I'm using this script, this is for a specific tab named Sheet1 only.

function makeLink() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var aCell = ss.getRange("D1"), value = aCell.getValue();
    aCell.setValue('=HYPERLINK("https://sellercentral.amazon.com/hzefad/orders/&orderId='+value+'","'+value+'")');  
    var aCell = ss.getRange("D2"), value = aCell.getValue();
    aCell.setValue('=HYPERLINK("https://sellercentral.amazon.com/hzefad/orders/&orderId='+value+'","'+value+'")');  
    var aCell = ss.getRange("D3"), value = aCell.getValue();
    aCell.setValue('=HYPERLINK("https://sellercentral.amazon.com/hzefad/orders/&orderId='+value+'","'+value+'")');  
    var aCell = ss.getRange("D4"), value = aCell.getValue();
    aCell.setValue('=HYPERLINK("https://sellercentral.amazon.com/hzefad/orders/&orderId='+value+'","'+value+'")');  
    var aCell = ss.getRange("D5"), value = aCell.getValue();
}

Is there a way to to use looping for me to shorten my script?

google-apps-script google-sheets loops javascript
1 Answers

You can use something like this:

i = 1
while (true) {
    var range = ss.getRange("D" + i);
    var value = range.getValue();
    if(value == "") {
        break;
    }
    range.setValue('=HYPERLINK("https://sellercentral.amazon.com/hzefad/orders/&orderId='+value+'","'+value+'")');
    i++;
}
user_103581210
Related