How to add all values in Google Sheets?

Dear Thunkers,
I’m trying to add all the values in one column of a Google Sheet I’m using as my database, and then find the average of all the values, but I don’t know any way to do it. If anyone has a suggestion or solution, it would be nice! Thanks!

Do you want the easy way or the hard way?

The easy way is to create the average formula in the Google Sheet and then access that cell from Thunkable.

If you want the hard way, let me know. :wink:

1 Like

Yes, Just create a average formula in the column you want and get the value from there

@1685249d1m, if you want to get the average value of a column using Thunkable blocks, you can use a Data Source block to get your column, then use List blocks to get the average. As you can see in this screenshot, there are a lot of values you can get from a list of numbers! For this block, ‘average’ = ‘mean’, ie. the sum of all of the values divided by the number of values.

Edit: you do have to cast your values as strings first. I have amended my blocks to show a simple function you can add to your app which will allow you to convert a list of numbers to be recognized as numbers in your app:

2 Likes

Hey Jane,
I tried your solution but for some reason when I attempted to display the variable into a label, instead it showed a string of random numbers much larger than the average. Do you have any ideas why this is so? Thanks!

1 Like

The hard way would be useful as I’m having difficulties averaging the columns, because then the average is displayed in my list viewer, which isn’t something I want. :cry:

1 Like

The block [List of values] assumes the column content is text this is why you cannot add them together.

You need to get [List of values] and loop through it one by one adding them to a variable and add to it +0 to force the content to contact nvert to number. Then decide the total by the number of items in the [List of values] list to get the average.

This is the hard way.

2 Likes

Do you know how I could loop or what blocks I should use to accomplish this? Thanks!

1 Like

You can try CSV export with SQL query to get formatted data from the Google Sheets.
According to your need you want the sum of total cell values of a column. So for that you can use

https://docs.google.com/spreadsheets/d/{Key}/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT%20Sum({Column})

For Example : https://docs.google.com/spreadsheets/d/1_ztoqpbM1XXXTHISISNOTANACTUALSHEETIDXX0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT%20Sum(A)

Here A in Sum(A) is the first column if my sheet

See this


My table has the values from 1 to 6

So this is the average when this routine runs
image

1 Like

Another solution is to convert the actual values in the list to numbers then use the average function in the list block to get the same.
See this


It produce the same result.

2 Likes

Ah, okay I understand now. Thanks, this helped a lot!

1 Like

Ding ding ding! @muneer comes through with the hard way. :slight_smile:

Honestly, I’d probably just create a new column in the Google sheet and store the average there. But now you have a couple options.

2 Likes

Thanks @muneer and @tatiang - I suppose I should have tested my blocks first!

@1685249d1m, here is a function you can use to convert any list to be recognized as numbers in your app:

I’ll update my original message with the code that works :sweat_smile:

2 Likes