scratch/add_test_tab.py

import sys, os, anyio, logging
sys.path.insert(0, os.path.abspath('.'))
logging.basicConfig(level=logging.INFO)

def add_tab():
    from environments.trace_env.tools.sheets_tool import get_sheets_service, get_or_create_ledger
    service = get_sheets_service()
    spreadsheet_id = get_or_create_ledger(service)
    
    # Add a new tab
    requests = [
        {
            'addSheet': {
                'properties': {
                    'title': 'Test_Tab_2'
                }
            }
        }
    ]
    try:
        service.spreadsheets().batchUpdate(
            spreadsheetId=spreadsheet_id,
            body={'requests': requests}
        ).execute()
        print("Added new tab!")
    except Exception as e:
        print(e)
        
    # Write data to the new tab
    body = {
        'values': [
            ["Date", "Vendor", "Category", "Amount", "Payment Method", "Order ID", "Notes", "Message ID"],
            ["2023-01-01", "Zomato", "Food", "500", "UPI", "ORD123", "Lunch", "MSG123"]
        ]
    }
    service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id,
        range="Test_Tab_2!A1:H2",
        valueInputOption="RAW",
        body=body
    ).execute()
    print("Added data to new tab!")

add_tab()