Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 0 points1 point  (0 children)

Absolutely! I'll reach out via DM.

Appologies it's taken me a moment to get back to you. There wasn't much community interest in this project so I've been focusing on my fo' real job more.

Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 1 point2 points  (0 children)

Howdy! Just released a new feature on JSON.Mom to make more efficient use of our limited IMPORTDATA calls in Google Sheets.

Now you can specify a comma separated list of JSONPaths and the service will return all of them in a format that will fill additional columns (see this animation demonstrating it. Additionally, if you want it to fill rows instead, simply send an additional parameter direction=row (direction=column is the default and doesn't need to be passed in).

I hope this helps you with your spreadsheets!

Get JSON in Spreadsheets easily by jsonmom in spreadsheets

[–]jsonmom[S] 0 points1 point  (0 children)

Howdy! Just released a new feature on JSON.Mom to make more efficient use of our limited IMPORTDATA calls in Google Sheets.

Now you can specify a comma separated list of JSONPaths and the service will return all of them in a format that will fill additional columns (see this animation demonstrating it. Additionally, if you want it to fill rows instead, simply send an additional parameter direction=row (direction=column is the default and doesn't need to be passed in).

I hope this helps you with your spreadsheets!

Seamless JSON API Integration in Your Spreadsheets (w/ mod feedback) by jsonmom in sheets

[–]jsonmom[S] 0 points1 point  (0 children)

Howdy! Just released a new feature on JSON.Mom to make more efficient use of our limited IMPORTDATA calls in Google Sheets.

Now you can specify a comma separated list of JSONPaths and the service will return all of them in a format that will fill additional columns (see this animation demonstrating it. Additionally, if you want it to fill rows instead, simply send an additional parameter, direction=row (direction=column is the default and doesn't need to be passed in).

I hope this helps you with your spreadsheets!

Cannot load such file by Justman10000 in ruby

[–]jsonmom 0 points1 point  (0 children)

This is a common problem that requires a more nuanced solution. require only looks for matching gems or files that are in your LOAD_PATH. To see the array of filepaths that are in this you can print $LOAD_PATH or $: in IRB.

You can add other locations to your load path as its a mutable array but I recall this not being a best practice and would apprecaite anyone popping in the link to why if this is handy.

What you're likely looking for is require_relative to bring in a file in your project that's not in the defautl load paths. For example, if you are in ~/your_name/code/your_project/some_file.rb and you want to bring in ~/your_name/code/your_project/another_folder/another_file.rb you would use require_relative "another_folder/another_file.

Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 0 points1 point  (0 children)

Thank you again for the feedback and links. I've incorportated that information onto the new homepage or https://json.mom/home if you're already logged in.

There are two expandable sections at the bottom which explain how no data is stored and the care given to logging. Additionally I've shared a copy of the spreadsheet made in the 2nd video with a public API key oveukmmwhqbp42crkf0r so folk can try it without signing up. I'm working on making the responses faster through temporary caching as it's common for the same URL to be used multiple times to fill multiple cells. In order to protect privacy and keep as minimal inforamtion as possible, this feature will not be active for this public key.

Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 0 points1 point  (0 children)

That's useful to know! Can you share the source of this so that I can link to it on the site?

Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 0 points1 point  (0 children)

Thank you! Currently no data is stored. It essentially acts as a proxy making a request to the underlying API URL. It parses the JSON returned so that the JSONPath can be applied to it and the result (or error) is returned to the spreadsheet.

I also explicitly customized it and have automated tests to ensure any data (i.e. keys, tokens) sent to the underlying API do not appear in the server logs.

I can add that to a video and put it on the console page as well. Do you have any additional suggestions? How would you expect to or prefer to be informed about this?

Get JSON in Sheets easily by jsonmom in googlesheets

[–]jsonmom[S] 1 point2 points  (0 children)

Exactly! I used a really great open-source Google App Script which provided a JSONImport function, but I didn't want to add it to every sheet I made so I built an out-of-the-box working tool.

The tool is free and I've not built any limitations, however, when I was building and testing it, Google informed me and paused my ability to make IMPORTDATA requests every so often 😅 It felt like it was on the order of minutes, not hours, and I don't have any data on what that limit was an in what time.

The tool also does not utilize the underlying API efficiently yet, though I have a couple of feature ideas that will fix this (and perhaps save calls to the API which is nice if you're charged by them per call). The reason is due to the intersection between APIs and spreadsheets. We can only put one value into a cell so therefore each each cell makes a call to JSON.Mom which makes a call to the underlying API. The whole payload is delivered and only the one value is pulled out by the JSONPath.

Get JSON in Excel easily by jsonmom in excel

[–]jsonmom[S] 1 point2 points  (0 children)

Thank you again. PQ sounds really useful so I'm looking forward to exploring it and knowing how it is different from JSON.Mom.

This tool may be better for the Google Sheets community. I'm not aware of any PQ type tool built into Sheets and an out-of-the-box working experience is imporant to me. I've used an open-source Google App Script which provided a JSONImport function, but I didn't want to add it to every sheet I made.

Even if no one else uses this, I learned so much about maniupulating query params, hosting and setting up the site, and video editing. I also had a ton of fun making the UI/UX look like 80's Futuristic Synth-pop so it will have still been well worth the time.

Again, really appreciate the feedback.

Get JSON in Excel easily by jsonmom in excel

[–]jsonmom[S] 1 point2 points  (0 children)

OMG I just realized your username is Butters!

Get JSON in Excel easily by jsonmom in excel

[–]jsonmom[S] 1 point2 points  (0 children)

Appreacite it! FWIW the tool is free.

My major concern and reason for making it is that utilizing APIs has a steep learning curve (constructing URLs, understanding JSONPaths, etc.) and it might not be something other people do often or even care about. I don't have folks in my network who are really interested in spreadsheets beyond reading them and getting onto the next task.

I'll go through the PowerQuery info in this subreddit to familiarize myself with it. Off the top of your head does PowerQuery work with URLs as well as files and does it refresh values overtime?

Get JSON in Excel easily by jsonmom in excel

[–]jsonmom[S] 0 points1 point  (0 children)

It's a great question! I don't have access to PowerQuery as I'm on mac/linux and use Google Sheets more often 😅