Converting a Spreadsheet to JSON with Neptyne

My all-time most popular post on this blog is from 10 years ago, about exporting a Google Spreadsheet as JSON. My approach in that post is to use a Google Apps Script (that I wrote). Developers still seem to reference that blog post and use the script, but I no longer maintain it. Apps Script has become increasingly harder to work with over the years, and last time I tried, I couldn't even figure out how to give my spreadsheet permission to run a script that *I* wrote.

What am I using now? Neptyne: an app co-founded by my former colleague Douwe Osinga that combines online spreadsheets with Python scripting. Every sheet can be easily manipulated as a Pandas data frame, and you can run Excel formulas from Python. To me, it's the best of both worlds!

So when I needed to convert a spreadsheet to JSON, I imported it into Neptyne from Google spreadsheets (which they make very easy with the "Import from Google Sheets" option). Then I opened the Python tab on the right side and ran this code:

print(Talks!A:J.to_dataframe(header=True).to_json(orient='records', indent=2))

Let's break that one liner down:

• Projects!A:J is a reference to the "Projects" worksheet and columns A through J. This is the same way it'd be referenced in Excel or Google Sheets.
• .to_dataframe(header=true) converts the sheet to a Pandas dataframe, including the first row as the header columns.
• .to_json(orient='records', indent=2) is a built-in function for Pandas dataframes that outputs pretty-printed JSON.

That gives me output like:

[
  {
    "title":"Containerizing Python Web Apps Workshop",
    "date":"2023-01-25T05:00:00.000Z",
    "description":"A workshop about containerizing Python apps.",
    "thumbnail":"https:\/\/pamelafox.github.io\/my-py-talks\/containers-workshop\/flask.png",
    "slides":"https:\/\/pamelafox.github.io\/my-py-talks\/containers-workshop\/",
    "video":null,
    "tags":"python",
    "location":"GDI (Virtual)",
    "cospeakers":null,
    "include":"yes"
  },
  ...

That's one line of Python code compared to many many lines of Apps Script. ❤️ it! Obviously, not everyone can easily move your spreadsheets from Google, but if you can, I think you'll find it's much more enjoyable to programmatically work with sheet data in Neptyne. And if it's not, give the Neptyne team your feedback, I'm sure they'd love to hear it.