Use python code block to change raw rows from Google Sheets into list of lists

by

I am working with data set I pull from Google sheets into Zapier. The data set looks like the below, and I am writing a code block to compare a users values against the all the values in the data set.

Bank   |   Product |   Bundle |   Restriction Geo |   Restriction Geo 2 |   Restriction Geo Val |   Restriction Type |   Restriction Type Val
_______________________________________________________________________________________________________________________________________________
Wells      Savings     Ind,Sp      State                                      NY,CA,NV                General                100%                                                  
JMorg       IRA        Ind         County                  CA                 Solano, Marin           Claims                 3
Goldman    Savings     All         Zip                                        94954,27717,38002       Credit Score           680
Wells      Savings     All         Zip                                        48441                   General                100%

The issue I am running into is that some of the cells are themselves CSVs, so I can’t pull each of the formatted columns individually and split them via .split(“,”). I think that what I need is to work with the raw rows, which have the following format:

[["Wells","Savings","Ind,Sp","State","","NY,CA,NV","General","100%"],["JMorg","IRA","Ind","County","CA","Solano,Marin",...,"48441","General","100%"]]

The raw rows come through as a single text string, rather than a list of lists, which is what I want. I tried running this regex pattern

(?<=\[).+?(?=\])

to extract each of the matches as line items, but this both keeps the opening and closing brackets, and leaves the values within quotes and not as lists. What I am trying to get is essentially a list of lists of lists, something like;

[[Wells,Savings,[Ind,Sp],State, ,[NY,CA,NV]...]]

This way I can do something like (I have already have this part written, and it works on test data, but that test data is already formatted as lists of lists).

for d in data_set:
   temp_val = []
   if d[0] == "Wells":
      temp_val = d[2].split(",")
      for t in temp_val:
         if t == "Ind":
            ###do something"
      temp_val.clear()

  • Would you be able to provide a minimal reproducible example of your code showing how the rows are extracted? I assume this isn’t done via Sheets API (which should return a list of lists if values of a range are extracted) as you’re pulling the raw rows from Zapier.

    – 

I believe that what you’re looking to do is loop through your lists and convert any comma separated value into a nested list inside the main lists. The below block of code will accomplish that.

Input and code:

lists= [['Wells', 'Savings', 'Ind,Sp', 'State', '', 'NY,CA,NV', 'General', '100%'], ['JMorg', 'IRA', 'Ind', 'County', 'CA', 'Solano,Marin', '48441', 'General', '100%']]
for l in lists:
    for idx,word in enumerate(l):
            if "," in word:
                    l[idx] = word.split(",")

Output:

[['Wells', 'Savings', ['Ind', 'Sp'], 'State', '', ['NY', 'CA', 'NV'], 'General', '100%'], ['JMorg', 'IRA', 'Ind', 'County', 'CA', ['Solano', 'Marin'], '48441', 'General', '100%']]

Leave a Comment