javascript - Google sheets script get cell Hyperlink - Stack Overflow

admin管理员组

文章数量:1414908

I'm trying to get the URL of a range of cells, but getLinkUrl always returns null.

Here is what I tried:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var url = sheet.getRange("A1").getRichTextValue().getLinkUrl();

This returns: null

var test = sheet.getRange("A1").getRichTextValue().getText(); 

This returns: text (which is the text in A1 so it's working, and so then I tried)

var test2 = sheet.getRange("A1").getRichTextValue().getLinkUrl(); 

This returns: null (I didn't understand why, and so tried this instead)

var test3 = sheet.getRange("A1:A10").getRichTextValues(); 

This returns:

[ [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ] ]

And so I'm lost, I really don't know how I can get the URL of a / many cell.

In the documentation, there is an explanation about getLinkUrl returning null if there are multiple URLs but I don't know how to extract only one URL.

The sheet I'm working with is really simple, there are words only in the A column,

appli
info
motivation
anime
dev perso
reflexion
funny
sad

I'm trying to get the URL of a range of cells, but getLinkUrl always returns null.

Here is what I tried:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var url = sheet.getRange("A1").getRichTextValue().getLinkUrl();

This returns: null

var test = sheet.getRange("A1").getRichTextValue().getText(); 

This returns: text (which is the text in A1 so it's working, and so then I tried)

var test2 = sheet.getRange("A1").getRichTextValue().getLinkUrl(); 

This returns: null (I didn't understand why, and so tried this instead)

var test3 = sheet.getRange("A1:A10").getRichTextValues(); 

This returns:

[ [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ],
  [ {} ] ]

And so I'm lost, I really don't know how I can get the URL of a / many cell.

In the documentation, there is an explanation about getLinkUrl returning null if there are multiple URLs but I don't know how to extract only one URL.

The sheet I'm working with is really simple, there are words only in the A column,

appli
info
motivation
anime
dev perso
reflexion
funny
sad

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Sep 25, 2021 at 17:24 Madar asked Sep 24, 2021 at 18:12 MadarMadar 24755 silver badges2121 bronze badges 3

• would you provide a sample with a link that we can edit? Thanks – David Leal Commented Sep 24, 2021 at 23:14
• I'm afraid that I don't understand what you need.. If it's something in the sheet, I only got a list of word in the A column : appli info motivation anime dev perso reflexion funny sad – Madar Commented Sep 25, 2021 at 17:22
• You can use getFormula() and it will return something like "=HYPERLINK(<link>)" then you can probably extract the link there. Please share a copy of your sheet for further details on your concern. – Jason E. Commented Sep 27, 2021 at 14:53

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 6

Although I'm not sure about your actual Spreadsheet, from I'm trying to get the url of a range of cell but it always return null to me., I thought that in your situation, the hyperlink might be set in a part of text of a cell. So, can you test the following sample script?

Sample script:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var res = sheet
  .getRange("A1")
  .getRichTextValue()
  .getRuns()
  .reduce((ar, e) => {
    var url = e.getLinkUrl();
    if (url) ar.push({url: url, text: e.getText()});
    return ar;
  }, []);
console.log(res)

Or, if you want to retrieve the URLs from multiple cells, you can also the following script.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var res = sheet
  .getRange("A1:A10")
  .getRichTextValues()
  .map((r, i) =>
    r.flatMap((c, j) =>
      c.getRuns().reduce((ar, e) => {
        var url = e.getLinkUrl();
        if (url) ar.push({ url: url, text: e.getText(), row: i + 1, col: j + 1 });
        return ar;
      }, [])
    )
  );
console.log(res)

• var test3 = sheet.getRange("A1:A10").getRichTextValues(); returns the object of RichTextValue[][]. By this, such result is obtained in your question. When you want to retrieve the URL, it is required to retrieve it from the object.

References:

• getRichTextValue()
• getRichTextValues()
• Class RichTextValue

Edit:

From the thing I try to obtain is this something like this : (which is the link to the cell A1) https://docs.google./spreadsheets/d/###/edit#gid=0&range=A1, I understood that you wanted to retrieve the hyperlink of the cell. In this case, getRichTextValue cannot be used. So, how about the following sample script?

Sample script:

function myFunction() {
  // Ref: https://stackoverflow./a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Liste");
  var range = sheet.getRange("A1:A10");
  
  var spreadsheetUrl = ss.getUrl();
  var sheetId = sheet.getSheetId();
  var startRow = range.getRow();
  var endRow = startRow + range.getNumRows() - 1;
  var startCol = range.getColumn();
  var endCol = startCol + range.getNumColumns() - 1;
  var cellLinks = [];
  for (var r = startRow; r <= endRow; r++) {
    for (var c = startCol; c <= endCol; c++) {
      var a1Notation = columnToLetter(c) + r;
      cellLinks.push({cell: a1Notation, url: `${spreadsheetUrl}#gid=${sheetId}&range=${a1Notation}`});
    }
  }
  console.log(cellLinks)
}

• In above script, the hyperlinks of cells "A1:A10" are returned. For example, when var range = sheet.getRange("A1"); is used, the hyperlink of only cell "A1" is returned.

本文标签： javascriptGoogle sheets script get cell HyperlinkStack Overflow