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:\Users\Jacob\PythonTestProject\venv\lib\site-packages\gspread\utils.py", line 592, in wrapper
return f(*args, **kwargs)
File "C:\Users\Jacob\PythonTestProject\venv\lib\site-packages\gspread\models.py", line 1127, in update
{'values': values, 'majorDimension': kwargs['major_dimension']}
File "C:\Users\Jacob\PythonTestProject\venv\lib\site-packages\gspread\models.py", line 236, in values_update
r = self.client.request('put', url, params=params, json=body)
File "C:\Users\Jacob\PythonTestProject\venv\lib\site-packages\gspread\client.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.
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.
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.
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)
priceList and skuList are put to the columns "A" and "B" of "Sheet1".