** Additional information I prepended to this question **

Let's imagine I want to enter a user named Alice into my Google Sheet.

After I type Alice into A2 then Alice will also appear in B2 (with the hyperlinks added as indicated in the Apps Script).

That works. That's good.

But...now, let's say I have a text document that has Alice on line 1, Bob on line 2, and Carl on line 3. Sure, I could copy Alice into A2, and then copy Bob into A3, and finally copy Carl into A4. That would work but, that would obviously be tedious.

Of course, instead, I want to copy all three lines (containing all 3 names) at once. Furthermore, I then want to paste Alice into A2, Bob into A3, and Carl into A4 at once (with the hyperlinks added as indicated in the Apps Script).

** My original question **

This Google Sheet --> Behance.net- sample spreadsheet indicates what I am trying to accomplish.

The following information appears, in a comment I created in a drawing, shortly after the sheet linked to above opens.

When I entered in aaa it worked. When I entered in bbb it worked. When I pasted in ccc, ddd, and eee it failed.

What does “it worked” mean? For example, it means immediately after I entered aaa into A3 a hyperlink was created and a copy of A3 appeared in B3.

What does “it failed” mean? For example, it means immediately after I pasted ccc, ddd, and eee into A6, A7, and A8 all at once (that is, not one at a time) nothing happened. That is, a hyperlink was not created and a a copy of A6, A7, and A8 failed to appear in B6, B7, and B8.

The "magic" above happens with the following Apps Script....

function onEdit(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;
const value = e.value;

console.log(value);

if (sheet.getName() == "RemcoE33" && range.getColumn() < 3 && range.getRow() > 1) {
    const urls = [`https://www.behance.net/${value}`,`https://www.behance.net/${value}/moodboards`];
    const richTextValues = [];
    console.log(urls);
    urls.forEach(url => {
    richTextValues.push(SpreadsheetApp.newRichTextValue()
        .setText(value)
        .setLinkUrl(url)
        .build())
    });
    sheet.getRange(range.getRow(), 1, 1, 2).setRichTextValues([richTextValues]);
}

}

CodePudding user response：

Try this to understand how it works and how to adapt your script:

function onEdit(e){
  Browser.msgBox(JSON.stringify(e))
}

When a single cell is modified:

{"authMode":"LIMITED","value":"c","oldValue":"A","user":{"email":"[email protected]","nickname":xxxxxxxxxx"},"range":{"columnEnd":3,"columnStart":3,"rowEnd":1,"rowStart":1},"source":{}}

When two or more cells are modified:

{"user":{"email":"[email protected]","nickname":"xxxxxxxxxx"},"range":{"columnEnd":3,"columnStart":3,"rowEnd":4,"rowStart":1},"authMode":"LIMITED","source":{}}

You can therefore identify the range at any time by:

var range = [e.range.columnEnd,e.range.columnStart,e.range.rowEnd,e.range.rowStart]

You can therefore proceed as you wish, for example:

function onEdit(e){
  var sh = e.source.getActiveSheet()
  for (var i = e.range.rowStart;i<=e.range.rowEnd;i  ){
    for (var j = e.range.columnStart;j<=e.range.columnEnd;j  ){
      // do something ... as
      sh.getRange(i,j).setBackground('red')
    }
  }
}

the 'new values' are also available by sh.getRange(i,j).getValue() (not the old ones)

Edit : application to your script

I think that you can preserve the core of your script (if it works as you expect) by this way

function onEdit(e){
  var sh = e.source.getActiveSheet()
  for (var i = e.range.rowStart;i<=e.range.rowEnd;i  ){
    for (var j = e.range.columnStart;j<=e.range.columnEnd;j  ){
      myFunction(sh,sh.getRange(i,j),sh.getRange(i,j).getValue())
    }
  }
}

function myFunction(sheet,range,value){
if (sheet.getName() == "RemcoE33" && range.getColumn() < 3 && range.getRow() > 1) {
    const urls = [`https://www.behance.net/${value}`,`https://www.behance.net/${value}/moodboards`];
    const richTextValues = [];
    console.log(urls);
    urls.forEach(url => {
    richTextValues.push(SpreadsheetApp.newRichTextValue()
        .setText(value)
        .setLinkUrl(url)
        .build())
    });
    sheet.getRange(range.getRow(), 1, 1, 2).setRichTextValues([richTextValues]);
}
}