def sync( all_sheets: bool = False, output_folder: str = "_players", sheet_app_id: str = typer.Option(envvar="GOOGLE_SHEET_APP_ID", default=""), sheet_name: str = typer.Option(envvar="GOOGLE_SHEET_NAME", default="Sheet1"), ): typer.secho("sync-players", fg="yellow") try: sa = SpreadsheetApp(from_env=True) except AttributeError: print_expected_env_variables() raise typer.Exit() try: spreadsheet = sa.open_by_id(sheet_app_id) except Exception: typer.echo( f"We can't find that 'sheet_app_id'.\n" f"Please double check that 'GOOGLE_SHEET_APP_ID' is set. (Currently set to: '{sheet_app_id}')" ) raise typer.Exit() if all_sheets: sheets = spreadsheet.get_sheets() else: try: sheets = [spreadsheet.get_sheet_by_name(sheet_name)] except Exception: typer.echo( f"We can't find that 'sheet_name' aka the tab.\n" f"Please double check that 'SHEET_NAME' is set. (Currently set to: '{sheet_name}')" ) raise typer.Exit() for sheet in sheets: data_range = sheet.get_data_range() table = Table(data_range, backgrounds=True) metadata = {} for item in table: for key in item.header: value = item.get_field_value(key) metadata[key] = value player = Player(**metadata) if not Path(output_folder).exists(): Path(output_folder).mkdir() player_filename = Path(output_folder, f"{player.slug}.md") if player_filename.exists(): post = frontmatter.loads(player_filename.read_text()) else: post = frontmatter.loads("") post.metadata.update(player.dict(by_alias=True)) player_filename.write_text(frontmatter.dumps(post))
def cli(ctx, gsheets_credentials, linkedin_username, linkedin_password, spreadsheet_id): ctx.ensure_object(dict) logging.basicConfig(level=logging.INFO) sa = SpreadsheetApp(gsheets_credentials) spreadsheet = sa.open_by_id(spreadsheet_id=spreadsheet_id) salesnav = Table.get_table_from_sheet(spreadsheet=spreadsheet, sheet_name='salesnav') li = LinkedIn() li.login(username=linkedin_username, password=linkedin_password) ctx.obj['li'] = li ctx.obj['salesnav'] = salesnav
class TestTableDatetimeField: http_mocks = mock_google_sheets_responses([ 'table_get_sheets.json', 'table_values_datetime.json', 'table_values_datetime.json' ]) sa = SpreadsheetApp(http=http_mocks) sheet = sa.open_by_id('whatever').get_sheet_by_name('Sheet1') data_range = sheet.get_range_from_a1("A1:B3") table = Table(full_range=data_range) def test_table_size(self): assert len(self.table) == 2 assert len(self.table.header) == 2 def test_first_row(self): row = self.table[0] birthday = row.get_field_value("birthday") assert row.get_field_value("name") == "foo" assert isinstance(birthday, datetime.datetime) assert birthday.year == 2021 assert birthday.month == 5 assert birthday.day == 1 def test_second_row(self): row = self.table[1] birthday = row.get_field_value("birthday") assert row.get_field_value("name") == "bar" assert isinstance(birthday, datetime.datetime) assert birthday.year == 2021 assert birthday.month == 9 assert birthday.day == 30 def test_set_datetime_values(self): for row in self.table: birthday = row.get_field_value("birthday") new_birthday = birthday + datetime.timedelta(days=1) row.set_field_value("birthday", new_birthday) # we then check the request put in the batch first_request = self.table.batches[0] first_value = first_request["updateCells"]["rows"][0]["values"][0][ "userEnteredValue"]["numberValue"] assert first_value == 44318.0 second_request = self.table.batches[1] second_value = second_request["updateCells"]["rows"][0]["values"][0][ "userEnteredValue"]["numberValue"] assert second_value == 44470.0 for batch in self.table.batches: assert batch["updateCells"]["rows"][0]["values"][0][ "userEnteredFormat"]["numberFormat"]["type"] == "DATE_TIME"
def test(ctx): credentials = ctx.obj['credentials'] spreadsheet_id = ctx.obj['spreadsheet_id'] logger.info('test called with credentials %s', credentials) sa = SpreadsheetApp(credentials) spreadsheet = sa.open_by_id(spreadsheet_id=spreadsheet_id) table = Table.get_table_from_sheet(spreadsheet=spreadsheet, sheet_name='salesnav') logger.info('header is %s', table.header) for row in table: logger.info('row %s', row.values) logger.info('name %s', row.get_field_value('first_name')) logger.info('invited_at %s', dt_deserialize(row.get_field_value('invited_at')))
class TestItem: http_mocks = mock_google_sheets_responses([ 'table_get_sheets.json', 'table_check_data_range.json', 'table_values.json', 'table_values.json', 'table_notes.json', 'table_backgrounds.json', 'table_font_colors.json' ]) sa = SpreadsheetApp(http=http_mocks) table_range = sa.open_by_id('whatever').get_sheet_by_name('Sheet1').get_data_range() table = Table( full_range=table_range, notes=True, backgrounds=True, font_colors=True ) item = Item( parent_table=table, row_index=0, header=['name', 'surname'], values=['john', 'doe'], notes=['note name', 'note surname'], backgrounds=['#ffffff', '#fff000'], font_colors=['#000fff', '#000000'] ) def test_get_field_value(self): assert self.item.get_field_value('name') == 'john' assert self.item.get_field_value('surname') == 'doe' def test_get_field_note(self): assert self.item.get_field_note('name') == 'note name' assert self.item.get_field_note('surname') == 'note surname' def test_get_field_background(self): assert self.item.get_field_background('name') == '#ffffff' assert self.item.get_field_background('surname') == '#fff000' def test_get_field_font_colors(self): assert self.item.get_field_font_color('name') == '#000fff' assert self.item.get_field_font_color('surname') == '#000000'
def sync_places(sheet_app_id, output_folder, sheet_name): output_folder = Path(output_folder) try: sa = SpreadsheetApp(from_env=True) except AttributeError: print_expected_env_variables() sys.exit(1) try: spreadsheet = sa.open_by_id(sheet_app_id) except Exception: click.echo( f"We can't find that 'sheet_app_id'. Please double check that 'LFK_GOOGLE_SHEET_APP_ID' is set. (Currently set to: '{sheet_app_id}')" ) sys.exit(1) try: sheet = spreadsheet.get_sheet_by_name(sheet_name) except Exception: click.echo( f"We can't find that 'sheet_name' aka the tab. Please double check that 'LFK_SHEET_NAME' is set. (Currently set to: '{sheet_name}')" ) sys.exit(1) # returns the sheet range that contains data values. data_range = sheet.get_data_range() table = Table(data_range, backgrounds=True) for item in table: name = item.get_field_value("name") address = item.get_field_value("address") slug = slugify(" ".join([name, address])) filename = f"{slug}.md" input_file = output_folder.joinpath(filename) if input_file.exists(): post = frontmatter.load(input_file) else: post = frontmatter.loads("") place = {} place["sitemap"] = False place["slug"] = slug # Our goal is to build a Place record without having to deal with # annoying errors if a field doesn't exist. We will still let you # know which field wasn't there though. if SHEETS_BOOL_FIELDS: for var in SHEETS_BOOL_FIELDS: try: place[var] = string_to_boolean(item.get_field_value(var)) except ValueError: click.echo( f"A column named '{var}' was expected, but not found.") if SHEETS_STRING_FIELDS: for var in SHEETS_STRING_FIELDS: try: place[var] = item.get_field_value(var) except ValueError: click.echo( f"A column named '{var}' was expected, but not found.") if SHEETS_URL_FIELDS: for var in SHEETS_URL_FIELDS: try: place[var] = verify_http(item.get_field_value(var)) except ValueError: click.echo( f"A column named '{var}' was expected, but not found.") food_urls = [] if "cuisine" in place and len(place["cuisine"]): place["cuisines"] = [ cuisine.strip() for cuisine in place["cuisine"].split(",") ] else: place["cuisines"] = None if place["cuisines"] and len(place["cuisines"]): place["cuisine_slugs"] = [ slugify(cuisine) for cuisine in place["cuisines"] ] else: place["cuisine_slugs"] = None if "neighborhood" in place and len(place["neighborhood"]): place["neighborhood_slug"] = slugify(place["neighborhood"]) if "delivery_service_websites" in place and len( place["delivery_service_websites"]): food_urls.append({ "name": "order online", "url": place["delivery_service_websites"] }) if FOOD_SERVICE_URLS: for var in FOOD_SERVICE_URLS: try: value = verify_http(item.get_field_value(var)) if len(value): food_urls.append({ "name": FOOD_SERVICE_DICT.get(var), "url": value }) except ValueError: click.echo( f"A column named '{var}' was expected, but not found.") place["food_urls"] = [ food_url for food_url in food_urls if food_url ] post.content = item.get_field_value("notes") post.metadata.update(place) input_file.write_text(frontmatter.dumps(post))
def get_table_from_sheet(self, spreadsheet): table = Table.get_table_from_sheet(spreadsheet, "Sheet1") assert len(table.items) == 5
def main(sheet_app_id, output_folder, sheet_name): output_folder = Path(output_folder) try: sa = SpreadsheetApp(from_env=True) except AttributeError: print_expected_env_variables() sys.exit(1) try: spreadsheet = sa.open_by_id(sheet_app_id) except Exception: click.echo( f"We can't find that 'sheet_app_id'. Please double check that 'LFK_GOOGLE_SHEET_APP_ID' is set. (Currently set to: '{sheet_app_id}')" ) sys.exit(1) try: sheet = spreadsheet.get_sheet_by_name(sheet_name) except Exception: click.echo( f"We can't find that 'sheet_name' aka the tab. Please double check that 'LFK_SHEET_NAME' is set. (Currently set to: '{sheet_name}')" ) sys.exit(1) # returns the sheet range that contains data values. data_range = sheet.get_data_range() table = Table(data_range, backgrounds=True) for item in table: name = item.get_field_value("name") address = item.get_field_value("address") slug = slugify(" ".join([name, address])) filename = f"{slug}.md" input_file = output_folder.joinpath(filename) if input_file.exists(): post = frontmatter.load(input_file) else: post = frontmatter.loads("") place = {} # Our goal is to build a Place record without having to deal with # annoying errors if a field doesn't exist. We will still let you # know which field wasn't there though. if SHEETS_BOOL_FIELDS: for var in SHEETS_BOOL_FIELDS: try: place[var] = string_to_boolean(item.get_field_value(var)) except ValueError: click.echo(f"A column named '{var}' was expected, but not found.") if SHEETS_STRING_FIELDS: for var in SHEETS_STRING_FIELDS: try: place[var] = item.get_field_value(var) except ValueError: click.echo(f"A column named '{var}' was expected, but not found.") if SHEETS_URL_FIELDS: for var in SHEETS_URL_FIELDS: try: place[var] = verify_http(item.get_field_value(var)) except ValueError: click.echo(f"A column named '{var}' was expected, but not found.") post.content = item.get_field_value("notes") post.metadata.update(place) input_file.write_text(frontmatter.dumps(post))
def sync_places( output_folder: str = "_places", sheet_app_id: str = typer.Argument(default="", envvar="LFK_GOOGLE_SHEET_APP_ID"), sheet_name: str = typer.Argument(default="", envvar="LFK_SHEET_NAME"), ): typer.secho("sync-places", fg="yellow") output_folder = Path(output_folder) if not output_folder.exists(): output_folder.mkdir() cuisine_aliases = aliases_to_cuisine() aliases = load_aliases() try: unknown_cuisines = aliases["unknown-cuisines"][0]["aliases"] except: unknown_cuisines = None try: sa = SpreadsheetApp(from_env=True) except AttributeError: print_expected_env_variables() raise typer.Exit() try: spreadsheet = sa.open_by_id(sheet_app_id) except Exception: typer.echo( f"We can't find that 'sheet_app_id'. Please double check that 'LFK_GOOGLE_SHEET_APP_ID' is set. (Currently set to: '{sheet_app_id}')" ) raise typer.Exit() try: sheet = spreadsheet.get_sheet_by_name(sheet_name) except Exception: typer.echo( f"We can't find that 'sheet_name' aka the tab. Please double check that 'LFK_SHEET_NAME' is set. (Currently set to: '{sheet_name}')" ) raise typer.Exit() # returns the sheet range that contains data values. data_range = sheet.get_data_range() table = Table(data_range, backgrounds=True) for item in table: name = item.get_field_value("name") address = item.get_field_value("address") neighborhood = item.get_field_value("neighborhood") slug = slugify(" ".join([name, neighborhood or address]), stopwords=STOPWORDS) filename = f"{slug}.md" input_file = output_folder.joinpath(filename) if input_file.exists(): post = frontmatter.load(input_file) else: post = frontmatter.loads("") place = {} place["sitemap"] = False place["slug"] = slug # Our goal is to build a Place record without having to deal with # annoying errors if a field doesn't exist. We will still let you # know which field wasn't there though. if SHEETS_BOOL_FIELDS: for var in SHEETS_BOOL_FIELDS: try: place[var] = string_to_boolean(item.get_field_value(var)) except ValueError: typer.echo( f"A column named '{var}' was expected, but not found.") if SHEETS_STRING_FIELDS: for var in SHEETS_STRING_FIELDS: try: place[var] = item.get_field_value(var) except ValueError: typer.echo( f"A column named '{var}' was expected, but not found.") if SHEETS_URL_FIELDS: for var in SHEETS_URL_FIELDS: try: place[var] = verify_http(item.get_field_value(var)) except ValueError: typer.echo( f"A column named '{var}' was expected, but not found.") food_urls = [] if "cuisine" in place and len(place["cuisine"]): place["cuisines"] = [ cuisine.strip() for cuisine in place["cuisine"].split(",") ] if unknown_cuisines: place["cuisines"] = [ cuisine for cuisine in place["cuisines"] if slugify( cuisine, stopwords=STOPWORDS) not in unknown_cuisines ] else: place["cuisines"] = None if place["cuisines"] and len(place["cuisines"]): place["cuisine_slugs"] = [] for cuisine in place["cuisines"]: cuisine_slug = slugify(cuisine, stopwords=STOPWORDS) place["cuisine_slugs"].append(cuisine_slug) if (cuisine_slug in cuisine_aliases and cuisine_aliases[cuisine_slug] not in place["cuisine_slugs"]): place["cuisine_slugs"].append( cuisine_aliases[cuisine_slug]) else: place["cuisine_slugs"] = None if "neighborhood" in place and len(place["neighborhood"]): place["neighborhood_slug"] = slugify(place["neighborhood"], stopwords=STOPWORDS) if "delivery_service_websites" in place and len( place["delivery_service_websites"]): food_urls.append({ "name": "order online", "url": place["delivery_service_websites"] }) if FOOD_SERVICE_URLS: for var in FOOD_SERVICE_URLS: try: value = verify_http(item.get_field_value(var)) if len(value): food_urls.append({ "name": FOOD_SERVICE_DICT.get(var), "url": value }) except ValueError: typer.echo( f"A column named '{var}' was expected, but not found.") place["food_urls"] = [ food_url for food_url in food_urls if food_url ] post.content = item.get_field_value("notes") post.metadata.update(place) typer.echo(dict(Place.validate(post.metadata))) input_file.write_text(frontmatter.dumps(post))