Automate tasks with python - 1. Updating Google Sheet

1-config.py

server = 'your_sql_server_address'
database = 'database_name'
username = 'your_username'
password = 'your_password'

2-import-libraries.py

import pymssql
import pandas as pd
import config

server = config.server
database = config.database
username = config.username
password = config.password

3-connect-to-database.py

conn = pymssql.connect(server, username, password, database)
cursor = conn.cursor(as_dict=True)

print("Connected to sql server")

cursor.execute("""
    SELECT
        CONVERT(DATE, orders.date) AS date,
        SUM(CASE WHEN (orders.application = 1) THEN 1 ELSE 0 END) AS website,
        SUM(CASE WHEN (orders.application = 2) THEN 1 ELSE 0 END) AS application,
        SUM(CASE WHEN (orders.application > 0) THEN 1 ELSE 0 END) AS total
    FROM
        orders
    where year(orders.date) > 2017 AND orders.status = 1 -- get only successfull orders of 2018
    GROUP BY
        CONVERT(DATE, orders.date)
    """)

4-save-to-dataframe.py

df = pd.DataFrame(columns=['date', 'website', 'application', 'total'])

for row in cursor:
    df = df.append(row, ignore_index=True)

conn.close()

print("Data loaded to pandas DataFrame")

df.sort_values(by = 'date', inplace=True)

5-open-google-sheet.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']

credentials = ServiceAccountCredentials.from_json_keyfile_name('your_json_file.json', scope)

gc = gspread.authorize(credentials)

wks = gc.open("Your Google Sheet Name").worksheet('Sheet Name')

print("Google sheet is ready now")

6-write-to-google-sheet.py

from gspread_dataframe import set_with_dataframe

set_with_dataframe(wks, df)

print("Data added to sheet successfully")

7-function.py

import pymssql
import pandas as pd
import gspread
import config
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import set_with_dataframe


server = config.server
database = config.database
username = config.username
password = config.password

def update_sheet():
  conn = pymssql.connect(server, username, password, database)
  cursor = conn.cursor(as_dict=True)
  print("Connected to sql server")

  cursor.execute("""
      SELECT
          CONVERT(DATE, orders.date) AS date,
          SUM(CASE WHEN (orders.application = 1) THEN 1 ELSE 0 END) AS website,
          SUM(CASE WHEN (orders.application = 2) THEN 1 ELSE 0 END) AS application,
          SUM(CASE WHEN (orders.application > 0) THEN 1 ELSE 0 END) AS total
      FROM
          orders
      where year(orders.date) > 2017 AND orders.status = 1 -- get only successfull orders of 2018
      GROUP BY
          CONVERT(DATE, orders.date)
      """)
  
  df = pd.DataFrame(columns=['date', 'website', 'application', 'total'])

  for row in cursor:
      df = df.append(row, ignore_index=True)

  conn.close()
  print("Data loaded to pandas DataFrame")

  df.sort_values(by = 'date', inplace=True)
  
  scope = ['https://spreadsheets.google.com/feeds']

  credentials = ServiceAccountCredentials.from_json_keyfile_name('your_json_file.json', scope)

  gc = gspread.authorize(credentials)

  wks = gc.open("Your Google Sheet Name").worksheet('Sheet Name')
  print("Google sheet is ready now")
  
  set_with_dataframe(wks, df)
  print("Data added to sheet successfully")

8-schedule.py

import schedule
import time

schedule.every().hour.do(update_sheet)

while True:
    schedule.run_pending()
    time.sleep(60)