How to LINK data from one Google Sheet Workbook to another Google Sheet Workbook.

 How to LINK data from one Google Sheet Workbook to another Google Sheet Workbook. 

1. Open the Google Sheet Workbook, in this example, A Google Sheet Workbook of my school forms name as "School Forms and Grades TEMPLATE"

- SCHOOL FORMS TEMPLATE

- ELECTRONIC CLASS RECORD FOR SENIOR HS

2. Make a copy of these two Wotkbook and save it on your own Google Drive Folder. I suggest that you use Google Shared Drive instead of Google Drive. 

3. In School Forms and Grades TEMPLATE, Go to worksheet SF1 and start typing your students LRN in column B and NAMES in column C

4. Here, I want to import the LRN and NAMES located in column B and C of my Worksheet named "SF1". to other Google Sheet Workbook, in this example the Electronic Class Record renamed as "G11_1STSEM_SUBJ1"

5. Copy the URL (Uniform Resource Locator) of the School Forms and Grades TEMPLATE where the SF1 Worksheet is included.

6. You need to take also the Worksheet Name which in this case is SF1 and the Cell Range where the Data (LRN and Names) is located. 

6. In the ECR Worksheet, create a worksheet and name it as VARIABLES. Paste the URL of the School Forms and Grades TEMPLATE and put it inside the double quotation mark. (""). Here we put it on cell B2.

7. Now create another worksheet and name it as INPUT DATA and create the formula as shown below. Type it on the first number for your student. Make sure that the column and rows in School Forms and Grades TEMPLATE will match with your columns and rows here. 

The IMPORTRANGE formula is use to import a range of cells from a specified spreadsheet.

The formula is IMPORTRANGE("URL of the GOOGLE SHEET WORKBOOK","Worksheet Name!Cell Range")

In this example, our Spreadsheet URL is already defined in Cell B2 of the VARIABLES worksheet. 

The Worksheet name is SF1 

The Cell Range where our Data is located is B10:C49

Generate your own Automated Report Card with these formulas

Automate your Report Card with these easy Google Sheet formula. 

In this lesson you will learn how to use the formula INDEX and MATCH in Google Sheet. In order for you to do the tasks, I provided a sample TEMPLATE of SUMMARY and REPORT CARD which we are using in our centralized grading system in our school. 

Here are the Steps:

1. Open your Summary in Google Sheet. In our school we already have our own template for SUMMARY Grades. You can download our template below. 

SUMMARY GRADES TEMPLATE - DOWNLOAD HERE

Once you open the TEMPLATE, Make a copy of it and save it on your Shared Drive. 

This template comes with SF1, Report Card, SF5 and SF10 already. 

2. Go to CARD Template and Select cell range AH6:AJ7. This is a merge cells. This where we will put our data validation. After this, open Tools and click Data Validation. 

3. In Data Validation Window, select List from a range under Criteria

4, In the Data Validation  window, make sure that the Data Range is looking for the column where the LRN is listed. In this case it is located in Worksheet SUMMARY FINAL GRADES and Cell Range B13:B93. Click OK and click Save to save the data validation. 

5. At this point, when you click the drop down arrow, you should see the list of LRN of your students. This LRN is linked to SUMMARY FINAL GRADE Sheet. 

6. Next let us create the formula to get the data of stuents when we select a specific LRN in the drop down list. Select the cell Address for student's name (D10:G10). A merge cells to house the surname of the student. 

=INDEX('SUMMARY - FINAL GRADES'!C13:C93,MATCH($A$8,'SUMMARY - FINAL GRADES'!$B$13:$B$93,FALSE),1)

In the formula, you could see that INDEX and MATCH is used together to perform more advanced and dynamic lookups. 

7. Repeat the process to other data needed in your card. Just change the cell range of the index that correspond to the data needed. 

If you are interested about this template and automation in school forms, you may contact me by commenting below. 

RECORD YOUR GOOGLE MEET FOR FREE

 If you want to record your Google Meeting for free, there are different ways to do it. In this tutorial you will learn how to stream  it on your private FACEBOOK GROUP and record it at the same time.

Here are the requirements: 

1. A Facebook Group set to private settings where only your students are added as members. 

2. OBS Software - A free software used for live streaming. 

3. Your Google Meet. 

 Here are the steps

Dowload and Install OBS on your computer

1. Go to obs.org 

2. Click Download obs for WINDOWS. 

3. Install the OBS Software. In the obs welcome screen, click Next. 

2. In the License Information Screen, click Next

3. In the Choose Install Locaton, click Next

4. Wait until the OBS program to be installed on your computer. 

5. Check Launch OBS Studio and click Finish

Set-up your OBS to stream and record your Google Meeting

1. Open your Google Meeting and maximize its window. 

2. Here is your OBS Window. Take note that the recorded screen will show on you canvas

3. To get the screen you want to record, In your OBS Window, click the + Sign and choose Display Capture. 

4. Click OK in Create/Select Source Window. 

5. In the Properties for Display Capture, select Automatic on Capture Method and your default Display where your Google Meet Window is present then click OK. 

6. Now you should see your Google Meeting Window on your OBS. (If you are using one monitor, just minimize your OBS. 

7. Add Audo Output for in your Source

8. Click OK in Create / Select Source Window for audio output tjen select the defaul Device for your Audio Output and click OK 

9. Add also Audio output and select default device then click OK. 

10. In the Audio Mixer window, clicl the gear icon. 

11. Select Advanced Audio Properties

.

12. In  Advanced Audion Properties window, select  Monitor and Output for Input and Output Audio Capture. 

Your OBS is now ready to stream your Google Meeting. 

1. Open your Private Facebook Group 

2. Click the LIVE Video Button. 

3. Choose Go Live and click Select button. 

4. Type the title of the live video. You can include the Grade Level, Section, Topic and Quarter if you want so students can see it. 

5. In the Select a video source option, choose Streaming software and copy the Stream key by clicking the Copy button. 

6. Go back to your OBS window and click Settings. 

7. In the Settings window, select Stream Menu

8. In Stream Menu Choose Facebook Live and paste the Stream key you copied earlier in Facebook Live then click OK. 

9. Now. just click the stream button and you are ready to go. 

10. The green box below indicates that your connection status is Good. 

11. Go back to Facebook Live and click Go Live button. 

12. Do not close this window until your streaming ends. 

13. Now you can go back to your Google Meeting and start your meeting! once you are done with your meeting, Go back to OBS and click Stop Streaming. 

14. After clicking the Stop Streaming button, go back to Facebook Live and you will see the streaming automatically ends and your video was automatically saved in your Facebook Group. Now, only you and your students can view your Recorded Online Meetings.