Importing data to a new Sheet (part 4)

Update 17-03-17: Since originally writing these posts, I've learned more about what query() really does, so I'm updating some references.

Part 4 of an going series dealing with taking tweets about homework, sorting them, putting them on the course website, and creating Calendar entries. The story so far...

• Part 1: Automating homework tweets to calendar entries using IFTTT and scripts (the overview)
• Part 2: Sorting course tweets in Sheets using query() 
• Part 3: Publishing a Sheet to the web

This (mercifully short) post will talk about how to link data in a parent Sheet to a completely separate Sheet. I need to have a separate Sheet for each course to run my calendar script, but there are any number of reasons you might want to link Sheets instead of just making a copy. If you want to embed only certain columns or tabs in the new Sites or in Classroom, it's easier if you import the data into a new Sheet first. The other reasons all pretty much have to do with the fact that if you make a change to the data in the parent Sheet, the new Sheet will update as well; a copy is not linked and won't update.  

It's really easy to do. Create a new sheet (I call mine "CourseCode hw to calendar") and copy the following code into cell A1:

=query(IMPORTRANGE("1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ","Tweets for MCF3M!U:W"))

Importrange() will link to the data in a separate Sheet; it will update when you open the spreadsheet. (Query() lets the spreadsheet update without you having to open it, which is what automating is all about. sort/filter the data and is not strictly necessary here.) You will need to change both the red and the blue text for your Sheet.

• The red text is the spreadsheet key of the main sheet, which you get from its url: https://docs.google.com/spreadsheets/d/1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ/edit#gid=652985193
• The blue text is the name of the tab you are getting the data from, followed by a ! and the columns you want. In this case, it brings in the columns with the sorted and reverse-dated tweets.

Note: When you're setting up the spreadsheet for the first time, you need to give access to IMPORTRANGE() first.^*

Give permission, and wait for your new sheet to populate...

Once permission has been granted, you'll have a nice Sheet you can embed (or run a script on) with no worries. Any changes to the linked cells in the parent Sheet will automatically update in this one.

The final installment of this series will talk about the script to create calendar entries from this data.

^*At the beginning of the year, for each sheet I set up importing from the main one, I needed to copy the importrange part into another cell of the spreadsheet, click on the red error triangle, allow access, then delete that cell. When I was trying to grab screenshots for this post, new sheets already seemed to have access to the main one, which was a nice surprise. It looks like my setup time for new courses will be slightly reduced!