Converting Google Sheets to Markdown Files

sheets-to-md.py

# Takes in a file CSV file and outputs each row as a Markdown file with YAML front matter named after first column.
# Data in the first row of the CSV is assumed to be the column heading.
# Original work borrowed from: https://github.com/EvanLovely/csv_to_jekyll
# Adapted from https://www.bryanklein.com/blog/hugo-python-gsheets-oh-my/

# Import the python libraries.
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pathlib import Path
import os
import json


#Path to your credentials.json, downloaded from Google. JSON_DATA was the name of my Netlify environment variable. 
# Alternatively, you can use line 21 to link the file locally.
jsondict = json.loads(os.environ['JSON_DATA'])

# Use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
# add credentials to the account
creds = ServiceAccountCredentials.from_json_keyfile_dict(jsondict, scope)
# creds = ServiceAccountCredentials.from_json_keyfile_name('D:/Downloads/credentials.json', scope)
client = gspread.authorize(creds)

# Open the Google Sheet by ID. Remember to set the visibility to Public!
sheet1 = client.open_by_key("ID_OF_YOUR_SHEET").sheet1

# Extract all of the records for each row.
sheetdata1 = sheet1.get_all_records()

# Set location to write new files to.
outputpath = Path(__file__).parent("/content/submission/")

# Loop through each row...
for row_index, row in enumerate(sheetdata1):
    # Open a new file with filename based on the first column
    filename = str(row.get("title")).lower() + '.md'
    outputfile = outputpath / filename
    new_yaml = open(outputfile, 'w')
    print("File Converted")

    # Empty string that we will fill with YAML formatted text based on data extracted from our CSV.
    yaml_text = ""
    yaml_text += "---\n"
    
    # Set the Page title value.
    yaml_text += "title: " + row.get("title") + ".\n"
    # Loop through each cell in this row...
    for key,val in row.items():
      # Compile a line of YAML text from our spreadsheet keys and the value of the current value, followed by a linebreak. I've listed all my column headings, which correspond to the necessay frontmatter.
      if key == "author" or key == "description" or key == "major" or key == "tools" or key == "link" or key == "contact":
        cell_heading = str(key)
        cell_text = cell_heading + ': "' + str(val) + '"\n'
        # Add this line of text to the current YAML string.
        yaml_text += cell_text
        yaml_text = yaml_text.replace('"', '')
  
      
    # Write our YAML string to the new text file and close it.
    new_yaml.write(yaml_text + "---\n")
    new_yaml.close()