Pulling specific information from Google Sheets into different Google Site pages dynamically (not all info needed based on filtering)

Question

Is there a way to pull specific information from a Google sheet into a Google site? I’m using Google sheets as a database and need to pull based on specific sorting fields to create live schedules for individual groups on a website. Alternatively, I could program within Sheets (I think) a page for each user group that is pulling from the master page and then publish/embed each page if that makes sense?

e.g. My sheet contains standard day, start time, end time, location, and details, but there are fields for user groups (i.e. students, staff, property, kitchen, etc.) I want to pull the events only for the tagged group and post them on their group's website page). Note some events will be pulled for multiple pages. Thank you! Sarah

Answer 1

You can use gviz to get and query google sheet data. For instance https://stackoverflow.com/a/68948211/15019380

It uses json end point with this url

var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;

where id is the spreadsheet id an gid the sheet id.

You can use 3 modes :

• /gviz/tq?tqx=out:json
• /gviz/tq?tqx=out:csv
• /gviz/tq?tqx=out:html

You can add queries for instance &tq=SELECT+B%2CC%2CD+where+C+contains+%27S%27 (means before encoding SELECT B,C,D where C contains 'S')

Reference : query language