Add/Replace columns of your Google Sheet using Google API

add_columns_to_google_sheets.py

"""
Turn on the credentials in your account:
https://developers.google.com/sheets/api/quickstart/python

Put the redirect uri as http://localhost:9999/
"""

from __future__ import print_function

import argparse
import os.path
import pickle
from pprint import pprint

from google.auth.transport.requests import Request
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build


def auth_sheets(_credentials_file):
    # 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 = None
    if os.path.exists('token.pickle'):
        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_file, SCOPES)
            creds = flow.run_local_server(port=9999)
        # Save the credentials for the next run
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    return creds


def make_service(_credentials_file):
    return build('sheets', 'v4', credentials=auth_sheets(_credentials_file))


ap = argparse.ArgumentParser()
ap.add_argument("-sid", "--sheet_id", required=True,
                help="<Required> Google Sheets ID")
ap.add_argument("-sn", "--sheet_name", required=True,
                help="<Required> The sheet name")
ap.add_argument('-scv', '--sheet_column_values', nargs='+', help='<Required> Sheet Column Values to Add', required=True)
ap.add_argument("-sc", "--sheet_creds_path", required=False,
                help="The Google Sheets credentials file path", default='credentials.json')
ap.add_argument("-scna", "--sheet_column_name", required=False,
                help="The Google Sheets credentials file path", default="New Column")
ap.add_argument("-scn", "--sheet_column_number", type=int, required=False,
                help="The Google Sheets credentials file path", default=0)

args = vars(ap.parse_args())

sheet_id = args["sheet_id"]
sheet_name = args["sheet_name"]
sheet_column_values = args["sheet_column_values"]
creds_path = args["sheet_creds_path"]
sheet_column_name = args["sheet_column_name"]
sheet_column_number = args["sheet_column_number"]

# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE = make_service(creds_path)


def make_range(_sheet_name):
    return _sheet_name + '!A1:Z'


def gapi_load_sheet(_sheet_id, _sheet_name):
    # Call the Sheets API
    sheet = SERVICE.spreadsheets()
    result = sheet.values().get(spreadsheetId=_sheet_id,
                                range=make_range(_sheet_name)).execute()
    values = result.get('values', [])

    return values


def gapi_set_service_type_values(_sheet_id, _sheet_name, _values):
    body = {
        'values': _values,
    }
    result = SERVICE.spreadsheets().values().update(
        spreadsheetId=_sheet_id,
        range=make_range(_sheet_name),
        body=body,
        valueInputOption="USER_ENTERED"
    ).execute()

    return result.get('updatedCells')


def add_column(_sheet_values, _appended_values, _column_name="New Column"):
    _sheet_values[2] += [_column_name]

    for (index, row) in enumerate(_sheet_values[3:]):
        try:
            row += [_appended_values[index]]
        except IndexError:
            row += [_appended_values[len(_appended_values) - 1]]

    return _sheet_values


def replace_column(_sheet_values, _appended_values, _column_number, _column_name="Replaced Column"):
    if _column_number == 0:
        return _sheet_values

    try:
        _sheet_values[2][_column_number] = _column_name
    except IndexError:
        return add_column(_sheet_values, _appended_values, _column_name)

    for (index, row) in enumerate(_sheet_values[3:]):
        try:
            row[_column_number] = _appended_values[index]
        except IndexError:
            row[_column_number] = _appended_values[len(_appended_values) - 1]

    return _sheet_values


sheet = gapi_load_sheet(sheet_id, sheet_name)
pprint(f'SheetId: {sheet_id} | Sheet Name: {sheet_name} | Rows: {len(sheet)}')
pprint(f'New Values: {sheet_column_values}')
pprint("Current rows:")
pprint(sheet)

if sheet_column_number == 0:
    values = add_column(sheet, sheet_column_values, sheet_column_name)
values = replace_column(sheet, sheet_column_values, sheet_column_number, sheet_column_name)

pprint("Rows to be updated:")
pprint(values)
pprint(gapi_set_service_type_values(sheet_id, sheet_name, values))