As everyone is working from home, we are supposed to make reports and presentations so we tend to have a lot of google sheets with various data in each and so this was a problem faced by me as well, so thought to fix this using some google functions. I came across a function on how to merges 2 or more than it with one single spreadsheet where all your data is present.
Google is free for all and so we don’t think before we create new files for data in a different genre but when the month ends we need to get final reports ready. If you are reading this you’re like me, you’re the person who’s selectively organized. When it comes to the important things in life like having a roof over your head and being well-fed, you’re organized. But when it comes to things like file management, you simply don’t give a heck.
How to Connect data of 2 google sheets
So let us begin connecting 2 google sheet data, the first one will have a list of all the people attending the seminar, and the second one will have all the people attending with their unique id.
So now we need to connect the column B of Google Sheet 2 to Column C of Google Sheet 1
Syntax of Formulae with Explanation
=IMPORTRANGE(“spreadsheet_url”, range_string)
General Syntax to use the function of IMPORTRANGE
spreadsheet_url– The URL of the spreadsheet from where data will be imported.- The value of
spreadsheet_urlmust either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
- The value of
- range_string – A string, of the format “[sheet_name!]range” (e.g. “Sheet1!A2:B6” or “A2:B6”) specifying the range to import.
- The sheet_name component of range_string is optional; by default, IMPORTRANGE will import from the given range of the first sheet.
- The value of
range_stringmust either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
Adding formula to Sheet 1
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Vu47j10_uT1SQqIXLVmVKIsp9NZStoGdTZr-Dauwwws/edit#gid=0”, Sheet1!B2:B15)
Formula added in C2 of Google Sheet 2
After adding the formulae you’ll be asked to allow the access of the Google Sheet 2
After allowing access of the 2nd sheet you’ll find all the data visible in the Google Sheet 2 would be visible in Sheet1!C2:C15
So, now you can start using this new function in your daily use. That’s it have fun combining data from several sheets
Important Notes
- Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using
IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can useIMPORTRANGEto pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source. - If the data you are trying to import is too large, you may get an error.
Also read: Leave Tracking System on Google Sheets.
If you like to read more about other functions that can be used as helping for your daily work – Google Sheet Function List.
1 Comment