cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ek21
Level: Powered On

Quirky yet annoying data entry issue in GoogleSheets

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

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: Quirky yet annoying data entry issue in GoogleSheets

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.

Microsoft Employee
@8bitclassroom

View solution in original post

5 REPLIES 5
mr-dang
Level 10

Re: Quirky yet annoying data entry issue in GoogleSheets

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.

Microsoft Employee
@8bitclassroom
ek21
Level: Powered On

Re: Quirky yet annoying data entry issue in GoogleSheets

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
Level 10

Re: Quirky yet annoying data entry issue in GoogleSheets

@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

 

2018-01-17.png

 

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.

 

2018-01-17 (1).png

 

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.

 

2018-01-17 (4).png

2018-01-17 (3).png

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)

 

2018-01-17 (5).png

 

 

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. 

Microsoft Employee
@8bitclassroom
mr-dang
Level 10

Re: Quirky yet annoying data entry issue in GoogleSheets

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.

Microsoft Employee
@8bitclassroom

View solution in original post

ek21
Level: Powered On

Re: Quirky yet annoying data entry issue in GoogleSheets

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 Smiley Frustrated so a roundabout way of figuring out that it wasn't causing an issue anymore.

 

Thanks for your help once more, really appreciate it!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 173 members 4,625 guests
Please welcome our newest community members: