My cell F394 looks like this:
08/10/2023 And there is a link attached to it (not as hyperlink, but as RichText).
Data validation is active and allows for short date only in exactly this format (dd/mm/yyyy).
I want to obtain/log the URL that's attached via a script:
function logURLToCloudLogs() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange("F394");
var richTextValue = cell.getRichTextValue();
for (var i = 0; i < richTextValue.getTextRuns().length; i++) {
var textRun = richTextValue.getTextRuns()[i];
if (textRun.getLinkUrl()) {
var linkUrl = textRun.getLinkUrl();
Logger.log("URL:" + linkUrl);
return;
}
}
Logger.log("Cell contains no URL.");
}
But it results in error:
TypeError: Cannot read properties of null (reading 'getTextRuns') logURLToCloudLogs @ ShowDateEdit.gs:7
When I log the value of the cell via .getValue(), i get:
Sun Oct 08 00:00:00 GMT+02:00 2023
My suspicion is that, due to the data validation, apps script does not identify the string as richtext.
Can anyone help me successfully log the richtext & obtain the URL?
Sun Oct 08 00:00:00 GMT+02:00 2023 by getValues, I guessed that your cell value of "F394" might be the date object. In that case, such an error occurs. Because cell.getRichTextValue() is null. I guessed that this might be the reason for your current issue.In this case, when your showing script is modified, how about the following modification?
function logURLToCloudLogs() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange("F394");
var orgNumberFormats = cell.getNumberFormats();
cell.setNumberFormat("@");
var richTextValue = cell.getRichTextValue();
cell.setNumberFormats(orgNumberFormats);
var runs = richTextValue.getRuns();
for (var i = 0; i < runs.length; i++) {
var textRun = runs[i];
if (textRun.getLinkUrl()) {
var linkUrl = textRun.getLinkUrl();
Logger.log("URL:" + linkUrl);
return;
}
}
Logger.log("Cell contains no URL.");
}
In this modification, the cell format is changed to the text format. And, the richtectValue is retrieved. And, the cell format is changed to the original one.
I thought that the following modification might be able to be also used.
function logURLToCloudLogs() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange("F394");
var orgNumberFormats = cell.getNumberFormats();
cell.setNumberFormat("@");
var richTextValue = cell.getRichTextValue();
cell.setNumberFormats(orgNumberFormats);
var linkUrl = richTextValue.getLinkUrl();
if (linkUrl) {
Logger.log("URL:" + linkUrl);
return;
}
Logger.log("Cell contains no URL.");
}
By the way, Class RichTextValue has no method of getTextRuns(). In this case, it might be getRuns(). Please be careful about this.