CODEWITHSUNDEEP
google-apps-script
oldbigbuddha
oldbigbuddha

Reputation: 21

I can't get text from RichTextValue

I can't get a text value from RichTextValue which is from a date cell.

The target sheet looks like this:

|  date  | string | string |

I want to get a text value from first cell.

So I write this code:

const dataRange = sheet.getDataRange().getRichTextValues();
dataRange.forEach((row) => {
  const text = row[0].getText();
  Logger.log(text);
});

But the log was empty. I can't get a text value from only date cell.(I can get texts from other cells like row[1] or row[2])

How can I get a text value from a date cell?

Upvotes: 1

Views: 2708

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

You can set formats all cells to String, get their rich values, and restore formats back. Here is the code:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  
  // store orig formats of cells
  const formats_orig = range.getNumberFormats();
  
  // set all cells to String
  const formats_string = formats_orig.map(row => row.map(cell => '@'));
  range.setNumberFormats(formats_string);

  // or you can do it this way:
  // range.setNumberFormat('@'); 

  // grab rich values
  const dataRange = range.getRichTextValues();

  dataRange.forEach((row) => {
    const text = row[0].getText();
    Logger.log(text);
  });
  
  // restore formats
  range.setNumberFormats(formats_orig);
}

Perhaps it makes sense to apply String format to the first column only. I suppose you know how it can be done. Just in case:

const formats_string = formats_orig.map(x => ['@'].concat(x.slice(1)));

// or

sheet.getRange('A:A').setNumberFormat('@');

Upvotes: 2

Tanaike
Tanaike

Reputation: 201553

Issue and workaround:

In the current stage, it seems that with getRichTextValues, the date object and the numbers cannot be retrieved. When those values are retrieved, no values are returned. About "getRichTextValues doesn't retrieve number values", this has already been reported to Google issue tracker. Ref I thought that this issue tracker might be the same with your situation.

I thought that this might be related to that the text format of the part of characters of the date object and number cannot be changed as "RichText".

So, in the current stage, when you want to retrieve the date object as the string value, how about using getDisplayValues instead of getRichTextValues? When your script is modified, it becomes as follows.

Modified script:

const dataRange = sheet.getDataRange().getDisplayValues();
dataRange.forEach((row) => {
  const text = row[0];
  Logger.log(text);
});

References:

Upvotes: 3

Related Questions