Coverage for portality / lib / gsheet.py: 53%

1""" 

2General reusable tools for Google Sheets 

3""" 

4import re 

5 

6import gspread 

7from gspread.utils import rowcol_to_a1 

8from gspread_dataframe import set_with_dataframe 

9from oauth2client.service_account import ServiceAccountCredentials 

10 

11 

12def load_client(gdrive_key_path): 

13 # use creds to create a client to interact with the Google Drive API 

14 scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] 

15 creds = ServiceAccountCredentials.from_json_keyfile_name(gdrive_key_path, scope) 

16 client = gspread.authorize(creds) 

17 return client 

18 

19 

20def update_sheet_by_df(worksheet, df): 

21 worksheet.clear() 

22 set_with_dataframe(worksheet, df) 

23 

24 

25def range_idx_to_a1(start_row, start_col, end_row, end_col): 

26 return f'{rowcol_to_a1(start_row, start_col)}:{rowcol_to_a1(end_row, end_col)}' 

27 

28 

29def idx_to_column_letter(col_idx): 

30 return re.sub(r'\d+', '', rowcol_to_a1(1, col_idx))