Spaces:
Sleeping
Sleeping
File size: 2,428 Bytes
9863223 afa26c5 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 78 |
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()
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_ID = 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_ID
def col_letter(col_num):
"""Convert a column number to a column letter (1-indexed)."""
letter = ''
while col_num > 0:
col_num, remainder = divmod(col_num - 1, 26)
letter = chr(65 + remainder) + letter
return letter
def add_new_data(new_row, service, SPREADSHEET_ID, num_of_columns = 5):
"""Add new data to the spreadsheet.
new_row: list of data to be added. """
# read the existing data
range_to_read = f'Sheet1!A:{col_letter(num_of_columns)}'
result = service.spreadsheets().values().get(
spreadsheetId=SPREADSHEET_ID,
range=range_to_read
).execute()
values = result.get('values', [])
number_of_rows = len(values)
new_row = [new_row]
range_to_write = f'Sheet1!A{number_of_rows + 1}'
request_body = {
'values': new_row
}
response = service.spreadsheets().values().append(
spreadsheetId=SPREADSHEET_ID,
range=range_to_write,
valueInputOption='RAW',
insertDataOption='INSERT_ROWS',
body=request_body
).execute()
print(f"Added new row at position {number_of_rows + 1}")
if __name__ == "__main__":
service, SHEET_ID = get_sheet_service(local=True)
new_row = ["test1", "test2", "test3", "test4", "test5"]
add_new_data(new_row, service, SHEET_ID)
|