0

There exist google script that we can write a function for our spreadsheet.

My question is that I would put data, which is in specific format, to google spreadsheet cell, then I want google Spreadsheet would help me auto complete the cell (prepend "http://test.com").

For example :

I want to put data to some cell like :

0BwOZauWX0uR8bWY2d09FV2FaVHc

9WbOZauWU7uR8bWY8d75FV3FaVKj

other format data

And I hope my google spreadsheet could help me complete the else, namely, make the cell with the specify format like the would become :

http://test.com/0BwOZauWX0uR8bWY2d09FV2FaVHc

http://test.com/9WbOZauWU7uR8bWY8d75FV3FaVKj

other format data

I don't want to use the formula because it would waste another cell in the spreadsheet. So what could I do? what key word is this and is there an existing google script for me to achieve the goal ?

Radian Jheng
  • 667
  • 9
  • 20
  • very slightly related (10 results found with same tags plus "auto fill"): http://stackoverflow.com/questions/16859941/autofill-script-google-spreadsheet-script – cregox May 20 '14 at 13:40

2 Answers2

2

You could use a custom script to achieve this by linking it to the onEdit event.

function onEdit(event) {
  var sheet = event.source.getActiveSheet();
  var range = event.source.getActiveRange();
  if (sheet.getSheetName() == "Sheet1" && range.getColumn() == 1 && range.getValue().toString().trim() !== "" && range.getValue().indexOf("http:") != 0) {
    range.setValue("http://test.com/" + range.getValue());
  }
}

After this, you need link it to the onEdit trigger. Resources -> Current project's triggers

You can read this to understand triggers. Here is a working copy.

Konstant
  • 2,179
  • 16
  • 32
  • It seems that if i edit the cell in the second time, it would prepend again. And I want the activeCell must match the format then prepend the texts, is there any judgement i could use ? – Radian Jheng Apr 17 '14 at 02:28
  • Yeah, I missed that. You could check if the value starts with `http:` – Konstant Apr 17 '14 at 02:33
  • Good!! And I hope that when I edit the cell again which is prepended, the cell would return back to origin data without the prepended "http://test.com", how would you do that with the script ? – Radian Jheng Apr 17 '14 at 02:48
0

Code from @Konstant works but if you want to use it as Hyperlink change range.setValue("http://test.com/" + range.getValue()); to range.setValue('=HYPERLINK("http://test.com/' + event.value + '"; + \"' + event.value + '\")');

And if you want to let this look like a URL add this code below: range.setUnderline(true); range.foregroundColor(blue);

Mateusz
  • 23
  • 6