How to send API data from Python to Google Sheets

By

This article will help you understand how to automate Google Sheets and minimize manual work using Python API . I used the Google App script in my previous article to accomplish the same thing, but in response to user requests, I'm bringing up this subject now.

I hope you have a basic understanding of the Python language and Google Sheets. The setup and prerequisites are listed below.

Prerequisites

  1. Python 3.10.7 or greater
  2. Pip package management tool
  3. A Google Account
  4. A Google Cloud project
            4.1 In the Google Cloud console, go to Menu menu > IAM & Admin > Create a Project.             
            4.2 In the Project Name you have enter a descriptive name for your project
            4.3 Click Create
Next Step


Now Authorize credentials for a desktop application
  1. In the Google Cloud console, go to Menu menu > APIs & Services > Credentials
  2. Click Create Credentials > OAuth client ID
  3. Click Application type > Desktop app
  4. In the Name field, type a name for the credential. This name is only shown in the Google Cloud console
  5. Click Create. The OAuth client created screen appears, showing your new Client ID and Client secret
  6. Click OK. The newly created credential appears under OAuth 2.0 Client IDs.
  7. Save the downloaded JSON file as credentials.json, and move the file to your working directory
When you download your OAuth client, it appears as and rename to credentials.json





 


Install the Google client library

  • pip install google-api-python-client
  • pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
Below Python code will save on same directory where you have put credentials.json file .

from __future__ import print_function

import pickle

import os.path

from googleapiclient.discovery import build

from google_auth_oauthlib.flow import InstalledAppFlow

from google.auth.transport.requests import Request

import json

import os.path

from httplib2 import Http

from oauth2client import file, client, tools

from datetime import datetime

import requests

import pprint

import requests

import urllib.request

import socket

from collections import defaultdict

# If modifying these scopes, delete the file token.pickle.

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

curTime = f"{datetime.today()}"

# Api return in jSON data

apiURL= "Api url"

response = urllib.request.urlopen(apiURL).read()

jsonResponse = json.loads(response.decode('utf-8'))

# Function      :   WriteOutputFile

# Description   :   Writes the output google sheet file from 2 arrays - a list

#                   of cards that goes to 1 sheet and a list of tasks that goes

#                   to another sheet.

def WriteOutputFile(data_out, cklst_out, sheet_svc, sheet_id):

    # Clear out all Cards sheet columns from row 1 down. 

    status = sheet_svc.spreadsheets().values().clear(

        spreadsheetId=sheet_id, range='Tab Name1 !A2:R', body={}

    ).execute() 

    # Write the Cards data into the Cards sheet.

    status = sheet_svc.spreadsheets().values().update(

        spreadsheetId=sheet_id, valueInputOption='USER_ENTERED',

        range='Tab Name1!A2', body={'majorDimension': 'ROWS', 'values' : data_out}

    ).execute() 

    # Write the tasks data into the Tasks sheet.

    sheet_svc.spreadsheets().values().update(

        spreadsheetId=sheet_id, valueInputOption='USER_ENTERED',

        range='Tab Name1!A1', body={'majorDimension': 'ROWS', 'values' : cklst_out}

    ).execute()

    # Clear up the timestamp sheet.

    status = sheet_svc.spreadsheets().values().clear(

        spreadsheetId=sheet_id, range='Tab Name2 !A2:A', body={}

    ).execute()

    # Updated the timestamp.

    sheet_svc.spreadsheets().values().update(

        spreadsheetId=sheet_id, valueInputOption='USER_ENTERED',

        range='Tab Name2!A1:C',

        body={'majorDimension':'COLUMNS', 'values':[['Last Updated', curTime],['Updated By', "Cron JOB"]]}

    ).execute() 

# Function      :   GoogleAPICreds

# Description   :   Builds a connection with the Google APIs and returns

#                   a credentials object. 

def GoogleAPICreds(scopes):

    # Initialise.

    creds = None

    # File token.pickle stores the user's access and refresh tokens, and is

    # created automatically when authorization flow completes for the 1st time.

    if os.path.exists('token.pickle'): #  don't worry about this file will be auto generated while execute the this file

        with open('token.pickle', 'rb') as token:

            creds = pickle.load(token)

    # 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) # Its your main authentication file when you create a OAuth 2.0 Client IDs 

            creds = flow.run_local_server(port=0)

        # Save the credentials for the next run

        with open('token.pickle', 'wb') as token:

            pickle.dump(creds, token)

    return creds

def main():   

 """Shows basic usage of the Drive v3 API.

    Prints the names and ids of the first 10 files the user has access to.

    """

    creds = None

    # The file token.pickle stores the user's access and refresh tokens, and is

    # created automatically when the authorization flow completes for the first time. 

    creds = GoogleAPICreds(SCOPES) 

    sheetSvc = build('sheets', 'v4', credentials=creds)

    # Google sheet to which all the Checklists are written.

    outSheetId = 'Your sheet ID' 

    # https://docs.google.com/spreadsheets/d/Your sheet ID/edit#gid=xzxxxx

    # Initialise the output array and write the header row.

    dataOut = []

    for child in jsonResponse['records']:

        dataOut.append([child['RowData1'],child['RowData1'],child['RowData3'],child['....'], child['RowDataN']])   

    # Loop through all boards and dump them into the data out array.

    checkListOut = []

    checkListOut.append(["column1","column2","column3","...", "columnN"]) 

    # Close the output file.

    WriteOutputFile(dataOut, checkListOut, sheetSvc, outSheetId)

if __name__ == '__main__':

    main()

Comments

Post a Comment