Skip to content
Advertisement

Unable to insert 2d array within gspread

I’m trying to insert a 2D array in order to get two columns inserted into a sheet via gspread. I’m able to insert the individual lists fine, but inserting the array causes an error. Here’s my code.

def megaDepotScrape():
listings = 0
priceList = []
skuList = []
# Iterate through the listings on the page, printing the price per entry
for listings in range(0, 12):
    # Connect to the site to be scraped
    siteURL = "https://megadepot.com/catalog/lab-equipment/multiwell-plates/brand:brandtech/"
    response = requests.get(siteURL, headers=headers)   
    # with open('brandtech.html', 'wb') as fp:
    #     fp.write(response.content)
    # Cook the soup
    html_soup = BeautifulSoup(response.text, 'html.parser')
 
    # Find all containers with the appropriate class name
    # The 'strong' class 'hot' contains the price information
    price_containers = html_soup.find_all("strong", class_="hot")
    price = price_containers[listings]
    priceStr = list(price)
    priceList.append(priceStr)

    # Find all containers for the appropriate class name
    # The 'div' class 'product-wrapper' contains the SKU 
    sku_containers = html_soup.find_all("div", class_="product-wrapper")
    sku = sku_containers[listings]
    # The sku is stored in the 'data-variant' of the 'article' tag
    for data in sku.find_all("article"):
        skuData = data["data-variant"]
        skuList.append(skuData)

    # Iterate through the loop 
    listings += 1
    # Write both lists to the sheets document
    # Reference update() in docs
rows = [priceList, skuList]    
print(rows)
#sheet.update('A1', [list(e) for e in zip(*rows)])
sheet.insert_row(skuList)

Here’s the value of rows

[[['$81.57'], ['$80.91'], ['$91.63'], ['$91.63'], ['$455.20'], ['$196.90'], ['$282.60'], ['$146.10'], ['$97.22'], ['$166.70'], ['$287.30'], ['$237.50']], ['781411', '781415', '781412', '781416', '701355', '701330', '701346', '701352', '782153', '701354', '781347', '781345']]

And here’s the error I get

    sheet.update('A1', [list(e) for e in zip(*rows)])      
  File "C:UsersJacobPythonTestProjectvenvlibsite-packagesgspreadutils.py", line 592, in wrapper
    return f(*args, **kwargs)
  File "C:UsersJacobPythonTestProjectvenvlibsite-packagesgspreadmodels.py", line 1127, in update
    {'values': values, 'majorDimension': kwargs['major_dimension']}
  File "C:UsersJacobPythonTestProjectvenvlibsite-packagesgspreadmodels.py", line 236, in values_update        
    r = self.client.request('put', url, params=params, json=body)
  File "C:UsersJacobPythonTestProjectvenvlibsite-packagesgspreadclient.py", line 76, in request
    raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid values[0][0]: list_value {n  values {n    string_value: "$81.57"n  }n}n', 'status': 'INVALID_ARGUMENT'}```

I'm not sure if there's some kind of limit that I'm hitting from uploading so much, or if there's some kind of error. Please let me know. Thank you.

Advertisement

Answer

I believe your goal as follows.

  • From the following sample value.

    Here’s the value of rows

    [[['$81.57'], ['$80.91'], ['$91.63'], ['$91.63'], ['$455.20'], ['$196.90'], ['$282.60'], ['$146.10'], ['$97.22'], ['$166.70'], ['$287.30'], ['$237.50']], ['781411', '781415', '781412', '781416', '701355', '701330', '701346', '701352', '782153', '701354', '781347', '781345']]

  • I understood that the values of priceList and skuList might be the following values.

      priceList = [['$81.57'], ['$80.91'], ['$91.63'], ['$91.63'], ['$455.20'], ['$196.90'], ['$282.60'], ['$146.10'], ['$97.22'], ['$166.70'], ['$287.30'], ['$237.50']]
      skuList = ['781411', '781415', '781412', '781416', '701355', '701330', '701346', '701352', '782153', '701354', '781347', '781345']
    
  • You want to put the values of priceList and skuList to 2 columns.

Modification points:

  • In this case, the array is required to be as follows.

      [["a1", "b1"], ["a2", "b2"],,,]
    
  • When you want to insert the several rows with 2 columns, you can use insert_rows().

When this is reflected to your script, it becomes as follows.

Sample script:

client = gspread.authorize(credentials)
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name you want to put the values.
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)

# These values are from your question.
priceList = [['$81.57'], ['$80.91'], ['$91.63'], ['$91.63'], ['$455.20'], ['$196.90'], ['$282.60'], ['$146.10'], ['$97.22'], ['$166.70'], ['$287.30'], ['$237.50']]
skuList = ['781411', '781415', '781412', '781416', '701355', '701330', '701346', '701352', '782153', '701354', '781347', '781345']

# I modified below script.
row = [[e1[0], e2] for e1, e2 in zip(priceList, skuList)]
print(row) # You can confirm the value of "row".
sheet.insert_rows(row)
  • When you run this script, the values of priceList and skuList are put to the columns “A” and “B” of “Sheet1”.

References:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement