I'm trying to use an old python code I used about an year ago (in a different account) but now it's not working anymore and I just can't fix it.
def create_new_sheet(sheet_name, sheet_id):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'token.json'
creds = None
creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes = SCOPES)
SPREADSHEET_ID = sheet_id
service = build('sheets', 'v4', credentials=creds)
body = {
"requests":{
"addSheet":{
"properties":{
"title":f"{sheet_name}"
}
}
}
}
service.spreadsheets().batchUpdate(spreadsheetId = SPREADSHEET_ID, body = body).execute()
I already have the token, and with it I can read data without any problems, but when I try to create a new sheet, this error happens:
MalformedError: Service account info was not in the expected format, missing fields client_email.
I'm basically doing the same thing I did in the past but now I'm having this error. Could someone help me understand what's happening here please?
The error message that you are getting indicates that you are trying to do this using a service account and the service account key file that you are using called token.json does not the have the correct structure.
The way your code will go depends if you want to use OAuth with regular users or a service account.
This is the full code I used for testing with the service account method:
from googleapiclient.discovery import build
from google.oauth2 import service_account
def create_new_sheet(sheet_name, sheet_id):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'token.json'
creds = None
creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes = SCOPES)
SPREADSHEET_ID = sheet_id
service = build('sheets', 'v4', credentials=creds)
body = {
"requests":{
"addSheet":{
"properties":{
"title":f"{sheet_name}"
}
}
}
}
service.spreadsheets().batchUpdate(spreadsheetId = SPREADSHEET_ID, body = body).execute()
if __name__ == '__main__':
create_new_sheet('Test name', 'Spreadtsheet ID')
This is the structure that the service account key file called token.json should have:
{
"type": "service_account",
"project_id": "",
"private_key_id": "",
"private_key": "-----BEGIN PRIVATE KEY-----\n
PRIVATE KEY HERE
\n-----END PRIVATE KEY-----\n",
"client_email": "",
"client_id": "",
"auth_uri": "",
"token_uri": "",
"auth_provider_x509_cert_url": "",
"client_x509_cert_url": ""
}
Based on the error message it looks like you are missing the client_email field from the token.json file. Make sure to create a new one and download it correctly from the GCP console and try again.
In case you want to do it using OAuth authenticating a regular user that is an editor of the file this is the code that you should use instead:
from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
def sheets_batch_update(spreadsheet_id, title):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
# pylint: disable=maybe-no-member
try:
service = build('sheets', 'v4', credentials=creds)
requests = []
# Change the spreadsheet's title.
requests.append({
'addSheet': {
'properties': {
'title': title
},
}
})
body = {
'requests': requests
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=body).execute()
except HttpError as error:
print(f"An error occurred: {error}")
return error
if __name__ == '__main__':
sheets_batch_update('Spreadsheet ID', 'Test name')
If you use this method make sure to delete your current token.json file as the structure for this case will be different.