Making an list or dict from values in a linked Google Sheet

Anvil Q&A
1

Hi,

I just went through the Book Finder tutorial which pulls data from Google Sheets to display book names. I want to use the same thing in my app, to pull a sheet containing:
a)row of apartment numbers,
b)row of resident IDs

Each apartment number will have 1 or more IDs. I’d like to store the rows of apartment numbers and the associated IDs in a dictionary, where the IDs would be a list.

It seems there are two problems here:

  1. Get each row into a dictionary key-value entry
  2. Get each ID into a list, where multiple values are separated by commas

I have encountered a few difficulties here.
One, I can’t seem to get each row into a list… when I print the dictionary, the output is only the very row of the spreadsheet. Here is my code:

for r in self.worksheet.rows:
   apartmentsDict = {}
   apartmentsDict.update({r['Apartment']:r['IDs'])}
print(apartmentsDict)

If I run the print() command inside the for loop, it prints all the rows, but if I run it outside the for loop, I get only the last value. Why is that?

Second, as I understand, anything read from the spreadsheet will be a string, even if the cell contains only integers. So I guess I will need to run int() on the output of the second field, and then figure out how to get the values into a list…

2

Well, I just re-read my post and answered my own question… I keep re-instantiating the dict with each loop iteration… D’oh! Instantiating the dict outside the loop fixed that problem.

Now to convert the values into ints and put them into a list…

3

Here is a cleaner way to do it.

If the ids are stored as a string with numbers separated by commas, this should work:

apartments_dict = {}
for r in self.worksheet.rows:
    ids_str = r['IDs'].split(',')
    ids_int = [int(i) for i in ids_str]
    apartments_dict[r['Apartment']] = ids_int
4

Thanks. I used a slightly different syntax in the final line, but it seems to work:

apartments_dict = {}
for r in self.worksheet.rows:
    ids_str = r['IDs'].split(',')
    ids_int = [int(i) for i in ids_str]
    apartments_dict.update({r['Apartment']:ids_str})

I tried your code and it works the same. Is there any reason why one would be preferable to the other?

5

Your code creates a temporary dictionary with {...}, passes it to the update method, which uses it to iterate through all its items and add them to the dictionary. Then gets rid of the temporary dictionary.

My code adds one item to the dictionary.

No temporary data structures are created or destroyed, no methods are called, no iterations. It just does what it’s supposed to do rather than getting it as the side effect of something more unnecessarily complex.

Unless you cycle through thousands of rows, you will not have any noticeable effect. But as you get familiar with python you will realize that my line is easier to read. You look at it and immediately understand what it does, while looking at yours, you need to think a little to understand what’s going on or why was done that way.

6

Thanks for the explanation!