In chatting with an Administrative Assistant, I noted the frustration with them not knowing about Field Trips.

Hey, I thought. I’ve done a Bus Field Trip request form. So, what if I took the data from that and pushed it to a new spreadsheet with just the particular school’s request?

I created a new spreadsheet, wrote a quick QUERY(IMPORTRANGE) formula, and now they are all set. (I used Google Sheets for this.)

The formula goes in cell A2. (I copy and paste the header information, row 1, from the originating sheet).

=QUERY(IMPORTRANGE(“GOOGLE SHEET URL WITH THE DATA“,”TABNAME!A:R”), “SELECT * WHERE Col7=’SCHOOLNAME‘”)
Thus (some data truncated for privacy): =QUERY(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1……./edit#gid=1……2″,”D….w!A:R”), “SELECT * WHERE Col7=’Middle School'”)

Here are the import parts:

  • = to start a formula
  • QUERY – I want to limit the information that I’m pulling from another sheet
  • (IMPORTRANGE – brings the data from another spreadsheet
  • (URL – of the sheet that you are pulling from,
  • , – breaks for the next section
  • “Tab Name” – the name of the tab that you are pulling information from (Note the double quote marks here)
  • !
  • A:R – the column range of information that you are pulling from that spreadsheet (*Note the closing double quote marks)
  • ,
  • “SELECT * – this selects ALL (*Note the double quote marks)
  • WHERE Col7 – the tells the formula which column to match – (7 = Col E)
  • =
  • ‘Middle School’ (*Note the single quote marks around the EXACT text that you want to match)
  • ” – Close the double quotes
  • ) – Close the parenthesis from the one be QUERY and IMPORTRANGE

Once you do this you will see a error. Click on that to see the “Allow Access” button.

Then I went into the spreadsheet and shared it with the appropriate people. I have also warned them not to type into any cell that is synced. They can use text effects (like strikethrough), or filters, but they can’t type over anything (that breaks the sync and will remove all existing data).

This at least provides them with the field trips for which a bus is requested (which is most of them).