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
andskuList
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
andskuList
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
andskuList
are put to the columns “A” and “B” of “Sheet1”.