Conditionally set text in Google AppSheet

Question

I am evaluating Google AppSheet. I have a Google Sheet with the following structure:

| items                                           |
| date       | time | category | name             |
|------------|------|----------|------------------|
|            |      | sports   | leather football |
|            |      | sports   | basketball       |
| 11-11-2021 | 9:00 | sports   | tennis ball      |

I'm working to display this sheet as a card view with a list layout. The title should be the date/time. The subtitle should be the name. My challenge, as shown in the table above, is that sometimes, I do not have a date or time. In those scenarios, I would like to render the title as "Unknown". Basically, the psuedocode below shows what I want to do:

if (date exists) {
  title = 'Arriving on ' + date;
} else {
  title = 'Unknown';
}

I do not see a way to do this in a Google AppSheet though. I have to believe I'm missing something. I assumed I would be able to do it via a Format Rule. However, it doesn't seem like I can specify conditional text. I can only set things like the color, font, etc. But, I do not see a way to add something like the conditional I showed above.

How can I conditionally set text in a Google AppSheet?

score 1 · Accepted Answer · answered Nov 11 '21 at 20:23

1

Solution:

If you get the value of a cell and it's a valid date, it should return a Date object. So you can use this condition to validate the object type:

var date = range.getValue();

if (date.constructor.toString().indexOf("Date") > -1) {
  title = 'Arriving on ' + date;
} else {
  title = 'Unknown';
}

Sample check:

Reference:

Check if object type is Date

answered Nov 11 '21 at 20:23

CMB

4,950
1
4
16

1

I understand this. What I don't understand though is, how to create custom text in an App Sheet based on the value of one or more cells. It seems like you're forced to pollute your spreadsheet. If you want to handle these scenarios. Am I wrong? – Dev Nov 12 '21 at 12:52
You can set [conditional format rules](https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder) in Apps Script to format your range based on value. So for example, you can have one format if the value of the cell is "Unknown" and another format otherwise. – CMB Nov 12 '21 at 15:01
If you need something more specific, please upload a sample spreadsheet with inputs and expected output. – CMB Nov 12 '21 at 15:03

score 1 · Answer 2 · answered Nov 16 '21 at 01:40

Another Option is Appsheet's virtual column. In the Data section on the Columns tab. Click your data source and then click the add virtual column button. You can then use something like this formula.

IF(ISBLANK([items date]), "Unknown", CONCAT("Arriving On: ",[items date]))

name it something like items_date_formmated

Then display this column in your card view form and hide the original date column by unchecking the is visible option.

Repeat for the time

Conditionally set text in Google AppSheet

2 Answers2

Solution: