How to use pygsheets - 10 common examples

def main():
    gc = pygsheets.authorize(service_file='./avrae-0b82f09d7ab3.json')

    template = gc.open_by_key(TEMPLATE_SPREADSHEET_ID)  # open template
    new_sheet = gc.create("TESTNew CharacterTEST", parent_id=PARENT_FOLDER_ID)  # create new character sheet

    template.sheet1.copy_to(new_sheet.id)  # copy character sheet over
    new_sheet.del_worksheet(new_sheet.sheet1)  # delete default worksheet
    new_sheet.worksheet().title = "v1.3"  # pretty it a little

    feature_cell_index = 0

    feature_cell_index = do_race(new_sheet.worksheet(), feature_cell_index)
    feature_cell_index = do_class_and_level(new_sheet.worksheet(), feature_cell_index)
    email = input("Please enter your Google account email: ")
    new_sheet.share(email, role='writer')  # give control to user

def get_credentials():
    """Function to check for valid OAuth access tokens."""
    gc = pygsheets.authorize(outh_file="credentials.json")
    return gc

parser.add_argument('-rh','--replace', help='replace the header if exists', action='store_true')
args = parser.parse_args()
#csv_file = args.file
gs_link = args.gs_ws

#print(args.gs_ss)

__location__ = os.path.realpath(os.path.join(os.getcwd(), os.path.dirname(__file__)))
weekly_data = defaultdict(list) # each value in each column is appended to a list
with open(os.path.join(__location__,'data_final.csv')) as f:
    reader = csv.DictReader(f) # read rows into a dictionary format
    for row in reader: # read a row as {column1: value1, column2: value2,...}
        for (k,v) in row.items(): # go over each column name and value 
            weekly_data[k].append(v) # append the value into the appropriate list

gc = pygsheets.authorize()
wks = ''
if args.sheet_name:
    wks = gc.open_by_key(gs_link).worksheet('title',args.sheet_name)
else:
    wks = gc.open_by_key(gs_link).get_worksheet('id',args.gs_ss)

print "updating the sheet, ", wks.title

max_rows = 125
max_cols = 4  # cols with matrices
week_start_index = 5
colsDicts = []

def createDict(ilist):
    #print(ilist)
    nkey = "none"

def get_mock_cell(
    value, value_unformatted=None, column_number=1, row_number=1, format_type=None
):
    cell = mock.create_autospec(pygsheets.Cell)
    type(cell).col = PropertyMock(return_value=column_number)
    if format_type is None:
        format_type = (FormatType.CUSTOM, "")
    type(cell).format = PropertyMock(return_value=format_type)
    type(cell).row = PropertyMock(return_value=row_number)
    type(cell).value = PropertyMock(return_value=value)
    if value_unformatted is None:
        value_unformatted = value
    type(cell).value_unformatted = PropertyMock(return_value=value_unformatted)
    return cell

def repo():
    mock_worksheet = mock.create_autospec(pygsheets.Worksheet)
    cell1 = mock.create_autospec(pygsheets.Cell)
    type(cell1).col = PropertyMock(return_value=1)
    cell1.value = "This is Column 1"
    cell2 = mock.create_autospec(pygsheets.Cell)
    cell2.value = "Column2 is this One"
    type(cell2).col = PropertyMock(return_value=2)
    cells = [cell1, cell2]
    mock_worksheet.get_row.return_value = cells
    return Repository(pygsheets_worksheet=mock_worksheet)

with open('spreadsheet.json') as data_file:
            spreadsheet_json = json.load(data_file)
        with open('worksheet.json') as data_file:
            worksheet_json = json.load(data_file)
        spreadsheet_json["Sheets"].append(worksheet_json)

        cls.config = read_config(CONFIG_FILENAME)
        # cls.gc = mock.create_autospec(pygsheets.Client)
        # cls.gc.open_by_key.return_value = spreadsheet_json
        # cls.gc._fetch_sheets = [{'testssheetid': 'testssheettitle'}]
        # cls.spreadsheet = pygsheets.Spreadsheet(cls.gc)

        cls.gc.service.spreadsheets().get = mock.create_autospec(cls.gc.service.spreadsheets().get)
        cls.gc.open_by_key.return_value = spreadsheet_json
        cls.gc._fetch_sheets = [{'testssheetid': 'testssheettitle'}]
        cls.spreadsheet = pygsheets.Spreadsheet(cls.gc)

def test_open(self, mock_get):

        mock_response = mock.Mock()
        mock_response.execute.return_value = self.spreadsheet
        mock_get.return_value = mock_response

        spreadsheet = self.gc.open('testssheettitle')

        mock_get.assert_called_once_with(spreadsheetId=self.config.get('Spreadsheet', 'key'),
                                         fields='properties,sheets/properties,spreadsheetId')
        assert(isinstance(spreadsheet, pygsheets.Spreadsheet))

def test_user_credentials_loading(self):
        c = pygsheets.authorize(client_secret=self.base_path + '/client_secret.json',
                                credentials_directory=self.base_path)
        assert isinstance(c, Client)

        self.sheet = c.create('test_sheet')
        self.sheet.share('pygsheettest@gmail.com')
        self.sheet.delete()

def test_service_account_authorization(self):
        c = pygsheets.authorize(service_account_file=self.base_path + '/pygsheettest_service_account.json')
        assert isinstance(c, Client)

        self.sheet = c.create('test_sheet')
        self.sheet.share('pygsheettest@gmail.com')
        self.sheet.delete()

estimate_size = False
        if type(crange) == str:
            if crange.find(':') == -1:
                estimate_size = True
        elif type(crange) == tuple:
            estimate_size = True
        else:
            raise InvalidArgumentValue('crange')

        if estimate_size:
            start_r_tuple = format_addr(crange, output='tuple')
            if majordim == 'ROWS':
                end_r_tuple = (start_r_tuple[0]+len(values), start_r_tuple[1]+len(values[0]))
            else:
                end_r_tuple = (start_r_tuple[0] + len(values[0]), start_r_tuple[1] + len(values))
            body['range'] = self._get_range(crange, format_addr(end_r_tuple))
        else:
            body['range'] = self._get_range(*crange.split(':'))
        body['majorDimension'] = majordim
        body['values'] = values
        self.client.sh_update_range(self.spreadsheet.id, body, self.spreadsheet.batch_mode)