For example, if I were to type "cucumber" into B6 then it would be formatted with foo.com/cucumber in the following sheet...

https://docs.google.com/spreadsheets/d/1usCU2UutcC9jchLlM-oBZVqgdfw_ERNFjOfrU_t5hkc/edit#gid=2100307022

That is what I want.

But if I were to paste "cucumber" into B6 then it would not be formatted with foo.com/cucumber

I want to be able to paste in text—such as "abc"—into column B and have it formatted with foo.com/abc.

Here's the code...

function onEdit(e){
  urlMaker(e);
}

function urlMaker(e) {
  if(e.range.getSheet().getName()!= 'Sheet1' || e.range.rowStart <2 || e.range.columnStart !=2){return}
  e.range.setRichTextValue(SpreadsheetApp.newRichTextValue().setText(e.value).setLinkUrl('http://###.com/' e.value).build());
}

I found the following, but it doesn't look like it would do what I want.

how to apply script to entire columns in google spredsheet

Pasting a value into the column results in the following error in the execution log:

Exception: Unexpected error while getting the method or property setText on object SpreadsheetApp.RichTextValueBuilder.
    at urlMaker(Code:7:62)
    at onEdit(Code:2:3)

CodePudding user response：

For some reason when you paste a value into a cell, e.value is undefined.

You can get it working by swapping e.value to e.range.getValue() though:

function onEdit(e){
  urlMaker(e);
    console.log(e.range)
}

function urlMaker(e) {
  if(e.range.getSheet().getName()!= 'Sheet1' || e.range.rowStart <2 || e.range.columnStart !=2){
    return
  }
    const RTV = SpreadsheetApp.newRichTextValue()
    const values = e.range.getValues()
    const rtvs = []

    values.forEach(function(row) {
      rtvs.push([RTV.setText(row[0]).setLinkUrl('http://example.com/'   row[0]).build()])
    })

  e.range.setRichTextValues(rtvs);
}

_{NB: website was changed to example.com due to stack guidelines.}