Apostrophe in front of each answer in formula bar causing problems with formulas in other sheets

Support
1
  1. The Apostrophe in front of the answer, in the formula bar, on Google Sheets is causing formulas on other sheets that pull from the answers to not work. For instance, my formula to sum the amount of dry pounds of food received from Kroger Brandt Pike in the month of November will no longer work.

=sumifs('Retail Donations'!AF:AF,'Retail Donations'!$F:$F,"kroger-brandt-pike",'Retail Donations'!$D:$D,">=11/1/19",'Retail Donations'!$D:$D,"<=11/30/19")

The formula will recognize an answer for the month formatted as such, mm/dd/yy, but will not recognize an answer formatted with the apostrophe ‘mm/dd/yy. The same goes for an answer for the amount of dry pounds received. A number value, such as 50, will be recognized but ’50 will not. Is there a way to remove the apostrophe from the answer?

Here is a link to the submissions sheet and additional sheets using the answers https://docs.google.com/spreadsheets/d/1f_-mMLu0zz7rb97ugUE4Y20_4cpvDz__DPgi7yYSmjA/edit?usp=sharing

  1. I am using ODK Collect V1.23.3 on a Samsung Galaxy Tab A, android version 9

  2. I can manually format each column after every new submission to eliminate the apostrophe. However, I would prefer if I did not have to do this.

  3. Create a sum function that draws from answer values submitted to google sheets.

2

You can read more about why raw values are sent at Integers and dates sent by ODK Collect to Google Sheets as text - #2 by LN.

Unfortunately, it looks like while arithmetic and some functions in Google Sheets work on raw values, others (most?) functions don't. The change to raw values was made with the assumption that most calculations would still work but this doesn't seem to be correct. We should look into whether this is also the case with Excel and see whether we could only send certain types of values as raw.

In the mean time, one thing I've done in a similar context is to add new columns that are typed versions of the raw ones. In your case, let's say you add a new sheet called 'Formatted'. In the second cell of the first column, you could write =ARRAYFORMULA(int('Retail Donations'!AF2:AF)). Similarly, in the second cell of the second column, =ARRAYFORMULA(datevalue('Retail Donations'!$D2:$D)) would give the typed dates. Then your formula would be

=sumifs('Formatted'!A:A,'Retail Donations'!$F:$F,"kroger-brandt-pike",'Formatted'!B:B,">=11/1/19",'Formatted'!B:B,"<=11/30/19")

Hopefully that gives you some ideas to work with.

3

Hello Mcrollin.

I have the same problem, for me is complicate use the apostrophe. Now a simple sum is complicate.
Use the version 1.22.4 of odk collect , this version do not send the apostrophe.

Maybe the developers reevaluate the use of apostrophe, or give a metod to choose if use the apostrophe or not.

Best Regards.

4

Good news @Mcrollin, @famador, @sneike -- @Grzesiek2010 explored sending raw values only for types that could be misinterpreted and this turns out to be very doable. Collect v1.25 will only send raw values for select multiple, barcode and string questions. Other values will be formatted automatically by Google Sheets. I will make sure you're tagged on the beta notice so that you can try this out and confirm that it will work for your needs.

1 Like
5

Thats perfect, thanks @LN :blush: *

1 Like