Spaces:
Sleeping
Sleeping
| 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_ID = os.environ['SHEET_ID'] | |
| return service_account_info, SHEET_ID | |
| 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) | |