GoogleSheet

Write dataframe to Google Sheet

import pandas as pd
import gspread
from df2gspread import df2gspread as d2g
from google.oauth2.service_account import Credentials
from google.oauth2 import service_account
from google.auth.transport.requests import AuthorizedSession
from gspread_dataframe import set_with_dataframe

# ตัวอย่าง dataframe
d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

# location to json credential file ที่ download มา
credentials = service_account.Credentials.from_service_account_file(
    'C://test//key//gsheet_bot.json')

scoped_credentials = credentials.with_scopes(
        ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
        )

gc = gspread.Client(auth=scoped_credentials)
gc.session = AuthorizedSession(scoped_credentials)


spreadsheet_key = 'xxxxxxxxxxxxxxxx'

sheet = gc.open_by_key(spreadsheet_key).sheet1

# wipe existing data in sheet ลบข้อมูลเดิมที่มีอยู่ใน sheet ทั้งหมด
gc.open_by_key(spreadsheet_key).values_clear("Sheet1!A1:ZZ10000")

# เขียน dataframe ลง sheet
set_with_dataframe(sheet, df) 

Reference : https://www.psunnn.com/dataframe-to-google-sheet/

Last updated