Quirky yet annoying data entry issue in GoogleSheets

[ek21]

Hey everyone,

I've created an app which connects to a GoogleSheet through flow to enter a new entry. However, when I enter the data (which it does perfectly fine) somehow a ' is entered before all numbers in the entry. Due to this, when I try to retrieve the data, the app doesn't recognise it as a number. I don't want to have to keep going into the Sheet and formatting the entries as numbers.

Can anyone help??

Thanks

[mr-dang]

I'm not 100% certain on this, but I think GoogleSheets works like Excel in OneDrive/Dropbox in which using it as a connected datasource treats all columns as Text. The last time I used it, there had not been a way to validate columns to accept a certain type of data (value, text, boolean, etc.).

I switched to a beefier datasource so I could validate fields to accept a certain type of data.

[ek21]

Hi @mr-dang,

Thanks for the reply! Darn, that's annoying. Unfortunately we are a charity so don't have extra funding for a better datasource - GoogleSheets isn't ideal for sure!

Thanks for the heads up, might be something we have to deal with.

[mr-dang]

@ek21, It's been > a year since I used Excel as a datasource, and I just ran some new tests. I was wrong about Excel's validation.

Test 1: can I validate an existing table?

What if I used the existing data validation setting in Excel to tell it what to expect? 

Result: upon writing data back to the table, a value appears as a value

Test 2: what if I start with a blank table, can I tell it to take different data types?

This time I started with just the headers without data in any column. I did not use the data validation feature in Excel.

Result: values were written as values, text as text, date as date, boolean as boolean. Looks like I didn't need the data validation feature after all.

Test 3: what happens if I try to write a record with mismatching data?

I wrote text in the field for value, value for text, text for date, and text for boolean.

Result: PowerApps created the record without the "blue dot warning" stating that data was mismatching. It seems you can write anything to any column.

Test 4: how does a lookup perform?

I tried the following after writing data to a new table with the correct data types written to the correct columns:

First(Filter(Table3,id=1)).date
and
LookUp(Table3,id=1,date)

Result: The blue dot showed up and the lookup did not work until I wrapped 1 in quotes. Trying the data validation feature in Excel did not work either. Even if a column has only numerical values, lookups seem to use text equivalence when matching up records.

First(Filter(Table3,id="1")).date
and
LookUp(Table3,id="1",date)

TL;DR

What does this all mean?

You can use Excel if you want numbers to come out as numbers. Since it seems that you analyze the data inside the spreadsheet itself, this seems like it can be a solution for you. But for lookups inside PowerApps itself, you will need to convert values to text or vice versa.

First(Filter(Table3,Value(id)=1)).date
and
LookUp(Table3,Value(id)=1,date)

or

First(Filter(Table3,id=Text(1))).date
and
LookUp(Table3,id=Text(1),date)

Dropbox is free to use, you just need a copy of Excel.

I will be running the same tests about validation for Google Sheets so I'll get back to you on that. 

[mr-dang · Accepted solution]

I ran tests on Google Sheets a few different ways to try to force a value to be written. Everything came out as text including fields expecting boolean values (boolean values are formatted as TRUE/FALSE in Excel/Sheets and true/false in PowerApps).

I like Google Sheets a lot. Its speed is on par with CDS. It's free. Sharing is easy. Editing the raw data online is easy. These advantages make Google Sheets a viable solution for clients with simple needs. So I have some solutions for you:

• Add a helper column for large objects like galleries
• Use Value()/Text() to match fields when performing lookups

Solution 1: Add a helper column for large objects like galleries

1. Wrap AddColumns() around the Items property of a Gallery and add a helper column.
   
   

   AddColumns(datasource,"helpercolumn1",Value(originalcolumn))
   
   or
   
   AddColumns(datasource,"helpercolumn1",originalcolumn*1)

   
   Lately, I've been multiplying by 1 to get a value since it's easier to type and format in the formula.
   
   
2. Perform operations on the helper column instead of the original column.

Solution 2: Use Value()/Text() to match fields when performing lookups

1. This is the same thing I mentioned in the last post:
   

   LookUp(datasource,Value(id)=1,column)
   
   or
   
   LookUp(datasource,id=Text(1),column)

Let me know what you end up going with.

[ek21]

Hi @mr-dang,

Thanks again for your troubleshooting on this! I went with the helper column, but then realised halfway through that Powerapps is now recognising these as numbers so a roundabout way of figuring out that it wasn't causing an issue anymore.

Thanks for your help once more, really appreciate it!