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 main():
sa = SpreadsheetApp('secret.json')
spreadsheet = sa.open_by_id(SPREADSHEET_ID)
sheet = spreadsheet.get_sheet_by_name(SHEET_NAME)
current_plot_cell = sheet.get_range_from_a1(a1_notification='A2')
current_plot_cell.set_value(int(get_plot_count()))
last_updated_cell = sheet.get_range_from_a1(a1_notification='B2')
last_updated_cell.set_value(datetime.now().isoformat())
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
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')))
def connect_to_sheet(
requested_spreadsheet_id: str,
requested_sheet_name: str = constants.sheets.Default,
) -> model.Sheet:
"""Connect to the specified Google Sheet and return the requested sheet (default is "Sheet1")."""
# extract a logger
logger = configure.configure_logging()
# use sheetfu to load the spreadsheet with configuration in environment variables
sa = SpreadsheetApp(from_env=True)
# get the spreadsheet by its identifier and then extract the specific
# worksheet from it, with Google Sheets making the default worksheet "Sheet1"
spreadsheet = sa.open_by_id(requested_spreadsheet_id)
sheet = spreadsheet.get_sheet_by_name(requested_sheet_name)
# DEBUG: display details about the sheet
logger.debug(type(sheet))
logger.debug(sheet)
return sheet
def create():
data = request.get_json()
date = data['date']
time = data['time']
temperature = data['temperature']
humility = data['humility']
voltage = data['voltage']
sheetname = 'Sheet1'
file_name = date
creds = False
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=8080)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
param = {'q': 'mimeType="application/vnd.google-apps.spreadsheet"'}
service = build('drive', 'v3', credentials=creds)
results = service.files().list(
**param, pageSize=10,
fields="nextPageToken, files(id, name)").execute()
items = results.get('files', [])
spreadsheet_app = SpreadsheetApp('secrets.json')
sheet = None
if items:
for item in items:
if item['name'] == file_name:
sheet = spreadsheet_app.open_by_id(
item['id']).get_sheet_by_name(sheetname)
if not sheet:
sheet = spreadsheet_app.create(file_name, editor="<email>")
selected_spread_sheet = spreadsheet_app.open_by_id(sheet.id)
sheet = selected_spread_sheet.get_sheet_by_name(sheetname)
sheet.get_range(1, 1).set_value('Date')
sheet.get_range(1, 2).set_value('Time')
sheet.get_range(1, 3).set_value('Temperature')
sheet.get_range(1, 5).set_value('Voltage')
last_row = sheet.get_data_range().coordinates.number_of_rows + 1
sheet.get_range(last_row, 1).set_value(date)
sheet.get_range(last_row, 2).set_value(time)
sheet.get_range(last_row, 3).set_value(temperature)
sheet.get_range(last_row, 4).set_value(humility)
sheet.get_range(last_row, 5).set_value(voltage)
resp = jsonify(success=True)
return resp
except:
pass
sheet = spreadsheet.get_sheet_by_name(sheet_name)
data_range = sheet.get_range(row=1,
column=1,
number_of_row=len(myoutput),
number_of_column=(len(myoutput[0])))
data_range.set_values(myoutput)
# Google Sheet
sa = SpreadsheetApp('secret.json')
spreadsheet = sa.open_by_id('12BDEIWO_85BN6egolnUqIpHQ6yRPyx35VnccSCQD2Ag')
# Full data set
output = []
output = set_header()
for slot in schedule.all(order_by=['datestamp']):
output.append(list(slot.values()))
publish_data(output, 'FULL')
# By division data
for division in get_divisions():
#if division is not "Upper Farm": continue
output_by_division = []
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 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))