CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tai Jin
Tai Jin

Reputation: 11

sheet.range().getRichTextValue() returns null even though it is correctly set

I'm having a problem with rich text values in spreadsheets. The following code sets the rich text value (a URL) in a cell but getting the rich text value returns null. It appears that the problem is related to the fact that the rich text value text is numeric.

function SheetEdit(e) {
  let sheet = e.source.getActiveSheet();
  let row = e.range.getRow();
  let col = e.range.getColumn();
  let year = 2022;
  // The following getRichTextValue() returns correctly. Its text value is non-numeric.
  let folderId = sheet.getRange(row,1).getRichTextValue().getLinkUrl().split('/')[5];
  let folder = DriveApp.getFolderById(folderId);
  let filelist = folder.getFilesByName(fileName);
  if (filelist.hasNext()==true) {
    let file = filelist.next();
    let url = file.getUrl().split('?')[0]; // remove URL parameters
    // The following setRichTextValue() correctly sets the value in the spreadsheet.
    // Its text value (year) is numeric.
    let richText = SpreadsheetApp.newRichTextValue().setText(String(year)).setLinkUrl(url).build();
    sheet.getRange(row,col).setRichTextValue(richText);
    // However, the following getRichTextValue() returns null
    Logger.log(sheet.getRange(row,col).getRichTextValue().getText());
    Logger.log(sheet.getRange(row,col).getRichTextValue().getLinkUrl());
  }
}

Upvotes: 1

Views: 1701

Answers (1)

Aaron Dunigan AtLee
Aaron Dunigan AtLee

Reputation: 2072

This appears to be a known issue. One way to work around it is to change

SpreadsheetApp.newRichTextValue().setText(String(year))

to

SpreadsheetApp.newRichTextValue().setText("'"+year)

This will cause the year (number) to be interpreted/displayed by the spreadsheet as text (without the leading ') rather than as a number. When you call

sheet.getRange(row,col).getRichTextValue().getText()

you'll get 2022 as expected.

Upvotes: 2

Related Questions