Spaces:
Sleeping
Sleeping
File size: 2,774 Bytes
9863223 a18130b 9863223 afa26c5 9863223 d089d7b 9863223 d089d7b 9863223 a18130b d089d7b 9863223 d089d7b 9863223 d089d7b 9863223 d089d7b 9863223 d089d7b 9863223 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
import os
from google.oauth2 import service_account
from googleapiclient.discovery import build
import json
import os
def load_envs(local=False):
"""Load the environment variables."""
if local:
from dotenv import load_dotenv
load_dotenv(override=True)
service_account_info = json.loads(os.environ['GOOGLE_APPLICATION_CREDENTIALS_JSON'])
SHEET_ID1 = os.environ['SHEET_ID1'] # human-ai-sequential
SHEET_ID2 = os.environ['SHEET_ID2'] # human-ai-parallel
SHEET_ID3 = os.environ['SHEET_ID3'] # ai-human-sequential
SHEET_IDs = [SHEET_ID1, SHEET_ID2, SHEET_ID3]
return service_account_info, SHEET_IDs
def get_sheet_service(local=False):
"""Get the google sheet service object."""
service_account_info, SHEET_IDs = load_envs(local=local)
# verify the service_account_info
credentials = service_account.Credentials.from_service_account_info(
service_account_info,
scopes=['https://www.googleapis.com/auth/spreadsheets']
)
# build the service object
service = build('sheets', 'v4', credentials=credentials)
return service, SHEET_IDs
def col_letter(col_num):
"""Convert a column number to its corresponding Excel-style letter."""
letter = ''
while col_num > 0:
col_num, remainder = divmod(col_num - 1, 26)
letter = chr(65 + remainder) + letter
return letter
def add_or_update_row_at_fixed_position(row_id, new_row, service, SPREADSHEET_ID, num_of_columns=5):
"""
Add or update data at a fixed row position in Google Sheet.
Args:
row_id: int, the row number (1-based index) where the data should be written.
new_row: list, the new data to be added or updated.
service: Google Sheets API service instance.
SPREADSHEET_ID: str, the spreadsheet ID.
num_of_columns: int, the number of columns to consider (default: 5).
"""
# Ensure `new_row` has exactly `num_of_columns` elements
new_row = new_row + [""] * (num_of_columns - len(new_row)) if len(new_row) < num_of_columns else new_row[:num_of_columns]
# Determine the write range for the specified row
range_to_write = f"Sheet1!A{row_id}:{col_letter(num_of_columns)}{row_id}"
# Directly write the data to the specified row
response = service.spreadsheets().values().update(
spreadsheetId=SPREADSHEET_ID,
range=range_to_write,
valueInputOption='RAW',
body={"values": [new_row]}
).execute()
print(f"Data written to row {row_id}. Response: {response}")
if __name__ == "__main__":
service, SHEET_IDs = get_sheet_service(local=True)
new_row = ["test1", "test2", "test3", "test4", "test5"]
add_or_update_row_at_fixed_position(12, new_row, service, SHEET_IDs[0])
|