I'm trying to do batch update rows while writing data to a google spreadsheet using gspread library. When the following function get_content()
produces a single result, I can do batch update rows
flawlessly:
import gspread
import requests
from oauth2client.service_account import ServiceAccountCredentials
link = 'https://api.coinmarketcap.com/data-api/v3/cryptocurrency/listing'
params = {
'start': 1,
'limit': '200',
'sortBy': 'market_cap',
'sortType': 'desc',
'convert': 'USD,BTC,ETH',
'cryptoType': 'all',
'tagType': 'all',
'audited': 'false',
'aux': 'ath,atl,high24h,low24h,num_market_pairs,cmc_rank,date_added,max_supply,circulating_supply,total_supply,volume_7d,volume_30d,self_reported_circulating_supply,self_reported_market_cap'
}
def authenticate():
scope = [
"https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
return client
def get_content(s,link,params):
res = s.get(link,params=params)
for item in res.json()['data']['cryptoCurrencyList']:
yield item['id']
if __name__ == '__main__':
with requests.Session() as s:
s.headers['User-Agent'] = 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36'
client = authenticate()
sheet = client.open("testFile").sheet1
sheet.insert_row(['NAME'],1)
item_list = []
cell_list = sheet.range('A2:A201')
for item in get_content(s,link,params):
item_list.append(item)
for i, val in enumerate(item_list):
cell_list[i].value = val
sheet.update_cells(cell_list)
However, when the same function get_content()
produces multiple results, I don't find any way to do the batch update rows
. This time the range would be sheet.range('A2:D201')
def get_content(s,link,params):
res = s.get(link,params=params)
for item in res.json()['data']['cryptoCurrencyList']:
yield item['id'],item['name'],item['symbol'],item['slug']
How can I batch update rows while writing to a google sheet using gspread?
CodePudding user response:
In your situation, how about the following modification?
From:
item_list = []
cell_list = sheet.range('A2:A201')
for item in get_content(s,link,params):
item_list.append(item)
for i, val in enumerate(item_list):
cell_list[i].value = val
sheet.update_cells(cell_list)
To:
item_list = []
for item in get_content(s,link,params):
item_list.append(item)
sheet.update('A2:D201', item_list, value_input_option='USER_ENTERED')