Sep-30-2022, 11:26 PM
(This post was last modified: Oct-02-2022, 08:19 PM by mgallotti.
Edit Reason: Added code tags
)
Hello,
I've been working on updating a spreadsheet in Google drive. I'm trying to use gspread to do this. I've been able to find the spreadsheet by it's ID and can clear the contents of the spreadsheet, but I'm having problems updating it with a local csv file I have been using to update for my test. on the last line, worksheet.batch_update(requests) generates this error: Python type error of 'request' object is not iterable.
I wonder if I am going about this the wrong way?
Thank you
This is the script. This script is called from a Visual Studio project.
from __future__ import print_function
from datetime import date, timedelta
import pickle
import os.path
import sys
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2 import service_account
import google.auth.transport.requests
import requests
import gspread
# gspread way ************
# authenticate to Google Sheets with a service account credentials json file
# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
#SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SPREADSHEET_ID = '1dyyrjxx198sEJTOa9PA6tPGIINVi2PF8BGScuqDo093'
base = "C:/pyscrpts/"
#CREDS FILE FOR GSPREAD
gc = gspread.service_account(filename= 'c:\pyscrpts\creds.json')
csv_file_path = base + 'updates.csv'
def main():
sh = gc.open_by_key(SPREADSHEET_ID)
# get work book
worksheet = sh.sheet1
title = sh.title
#worksheet_list = sh.worksheets()
#for sheet in worksheet_list:
# print('sheetName: {}, sheetId(GID): {}'.format(sheet.title, sheet.id))
requests = google.auth.transport.requests.Request()
#clear an entire worksheet
worksheet.clear()
#Read csv and form request
with open(csv_file_path, 'r', encoding='UTF8') as csv_file:
csvContents = csv_file.read()
body = {
'requests': [{
'pasteData': {
"coordinate": {
"SheetId": SPREADSHEET_ID,
"rowIndex": "0",
"columnIndex": "0",
},
"data": csvContents,
"type": 'PASTE_NORMAL',
"delimiter": ',',
}
}]
}
worksheet.batch_update(requests)
I've been working on updating a spreadsheet in Google drive. I'm trying to use gspread to do this. I've been able to find the spreadsheet by it's ID and can clear the contents of the spreadsheet, but I'm having problems updating it with a local csv file I have been using to update for my test. on the last line, worksheet.batch_update(requests) generates this error: Python type error of 'request' object is not iterable.
I wonder if I am going about this the wrong way?
Thank you
This is the script. This script is called from a Visual Studio project.
from __future__ import print_function
from datetime import date, timedelta
import pickle
import os.path
import sys
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2 import service_account
import google.auth.transport.requests
import requests
import gspread
# gspread way ************
# authenticate to Google Sheets with a service account credentials json file
# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
#SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SPREADSHEET_ID = '1dyyrjxx198sEJTOa9PA6tPGIINVi2PF8BGScuqDo093'
base = "C:/pyscrpts/"
#CREDS FILE FOR GSPREAD
gc = gspread.service_account(filename= 'c:\pyscrpts\creds.json')
csv_file_path = base + 'updates.csv'
def main():
sh = gc.open_by_key(SPREADSHEET_ID)
# get work book
worksheet = sh.sheet1
title = sh.title
#worksheet_list = sh.worksheets()
#for sheet in worksheet_list:
# print('sheetName: {}, sheetId(GID): {}'.format(sheet.title, sheet.id))
requests = google.auth.transport.requests.Request()
#clear an entire worksheet
worksheet.clear()
#Read csv and form request
with open(csv_file_path, 'r', encoding='UTF8') as csv_file:
csvContents = csv_file.read()
body = {
'requests': [{
'pasteData': {
"coordinate": {
"SheetId": SPREADSHEET_ID,
"rowIndex": "0",
"columnIndex": "0",
},
"data": csvContents,
"type": 'PASTE_NORMAL',
"delimiter": ',',
}
}]
}
worksheet.batch_update(requests)