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))