CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheets
Franc
Franc

Reputation: 43

How to make a loop script for Google sheet?

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?

Upvotes: 2

Views: 105

Answers (1)

Martin Urbanec
Martin Urbanec

Reputation: 436

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++;
}

Upvotes: 1

Related Questions