プログラム

sheet_copy.py
#! /usr/bin/python
#
#	sheet_copy.py
#
#						Nov/27/2024
# ------------------------------------------------------------------
import sys
import os

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread/lib/python3.12/site-packages')
sys.path.append(package_path)

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread-dataframe/lib/python3.12/site-packages')
sys.path.append(package_path)

import pandas as pd
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
#
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")

# Google Sheets APIの認証情報
json_file = 'service_account.json'

# スプレッドシートのID
sheet_id = '1D1hk5LH6O3BlFehaBgnzfGt*****'

# 読み込み元と書き込み先のシート名

read_sheet_name = 'Sheet1'
write_sheet_name = 'Sheet2'

# Google Sheets APIの認証
gc = gspread.service_account(filename=json_file)

# スプレッドシートの読み込み
spreadsheet = gc.open_by_key(sheet_id)
worksheet = spreadsheet.worksheet(read_sheet_name)

# データの読み込み
#df = get_as_dataframe(worksheet, header=None)
df = get_as_dataframe(worksheet)
print(df)

ws_list = spreadsheet.worksheets()
print(ws_list)
sheet_names = []
for tt in ws_list:
	sheet_names.append(tt.title)
print(sheet_names)
if not write_sheet_name in sheet_names:
	spreadsheet.add_worksheet(title=write_sheet_name, rows=50, cols=10)
#
worksheet = spreadsheet.worksheet(write_sheet_name)
#set_with_dataframe(worksheet, df, include_column_header=False)
set_with_dataframe(worksheet, df)
#
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------
参考情報

Gspread の使い方 (シートのRead)
Gspread の使い方 (シートのCreate)