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
CodePudding user response:
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 ofRichTextValue[][]
. 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:
Edit:
From the thing I try to obtain is this something like this : (which is the link to the cell A1) https://docs.google.com/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.com/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.