CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kelvin Chong
Kelvin Chong

Reputation: 230

Creating links on the same cell with it's own value

I'm new to scripting and have been wondering about it. I recently ran into a big problem. I have a sheet containing 7000 columns that i need to edit

The workflow here is I have a series of web link 3xxxx-0987-xxx in column H for example

and I want to append the link of the website i want them to go to on the beginning of this series of code, meaning

it should become www.google.com/3xxxx-0987-xxx

Now I found this online, 

    //global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

//function onEdit(e) {
//  var activeRange = e.source.getActiveRange();

//  if(activeRange.getColumn() == 8) { 
//    if(e.value != "") { 
//      activeRange.setValue('=HYPERLINK("http://www.google.com/?folio='+e.value+'","'+e.value+'")');
//    }
//  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.google.com/?folio='+value+'","'+value+'")');  
}

So my issue here is, there is already nearly 7000 data in the columns, It creates a menu bar that I can click "create URL" and it would work. But I can't get it to work across a range, I would like to modify it to be able to just highlight all and click url and it would append by itself. Right now it works only one a single cell and I have 7000 of them :/

Any help? Thanks

I tried doing 

   function createURL() {
  var aCell = ss.getActiveRange(), value = aCell.getValues();
  aCell.setValue('=HYPERLINK("http://www.google.com/?folio='+value+'","'+value+'")');  
}

but I get a concatenated list of url for the whole range.

Upvotes: 0

Views: 97

Answers (1)

Kishan
Kishan

Reputation: 1810

Try the following script code:

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
};

function createURL() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var r = ss.getActiveRange();
  var v = r.getValues();
  for(var i=0;i<v.length;i++) {
    for(var j=0;j<v[0].length;j++) {
      v[i][j] = '=HYPERLINK("http://www.google.com/?folio='+v[i][j]+'","'+v[i][j]+'")'
    }
  }
  r.setValues(v);  
};

Upvotes: 1

Related Questions