Cara menggunakan dynamic importrange google sheets

Google Spreadsheet adalah sebuah software / perangkat lunak online / berbasis web yang dibuat oleh google yang digunakan untuk menyimpan, menampilkan dan mengolah data dalam bentuk baris dan kolom (seperti excel) yang dapat digunakan dan diolah secara bersama-sama (kolaboratif). Data pada google spreadsheet dapat diolah menggunakan rumus / formula tertentu.

Jika kita sudah memiliki beberapa file spreadsheet, sangat memungkinkan jika kita akan memasukan data yang sudah ada pada google spreadsheet ke dalam spreadsheet baru. Misalkan jika kita sudah memiliki beberapa file yang berisi nilai rata-rata dari sebuah survey yang dibuat menggunakan google form untuk berbagaimacam produk dan kita akan membuat rekap nilai kedalam file spreadsheet baru yang nilai / angkanya otomatis berubah menyesuaikan hasil survey online yang sudah kita buat, maka dapat kita lakukan dengan cara sebagai berikut :

Yes, assuming your data isn’t subject to change.

But what if you want to bring data into your sheet in a dynamic way; a way that recognizes when the source data changes and brings in those changes to the destination automatically?

This is where the amazing Google Sheets IMPORTRANGE function comes into play.

With this function, not only will we be able to bring in data dynamically, but we will be able to append multiple data sources into a single table.

The Data We Need

For this demonstration, the data needed for the report is in a file named “HR Information” which has two tabs: “HR 2010” and “HR 2020”.

“HR 2010” has people with entries dated till the end of 2010 while “HR 2020” has people with entries dated till the end of 2020.

The objective is to bring the data from these two sheets into a new Sheets file.  The goal is to have the data from these two sheets appended (i.e., stacked) atop one another, forming a single table.

Before we tackle the appending process, we need to step back and solve the problem of importing a single data source.

Importing Data with IMPORTRANGE

The IMPORTRANGE function allows you to point to another file and extract data from a defined range.

The syntax for the IMPORTRANGE function is:

IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url – The URL of the spreadsheet from where data will be imported. The value for spreadsheet_url must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
• 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 for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

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 use IMPORTRANGE to pull from any part of the source spreadsheet.  The access remains in effect until the user who is granted access is removed from the source.

If the data you are trying to import is too large, you may get an error.

Acquiring the Source File’s URL

When working with other Google Sheets files, it is necessary to obtain the URL (Uniform Resource Locator) of the source files to inform IMPORTRANGE as to the location of the data.

With the source file open, highlight and copy the ULR visible in the Address Bar at the top of the browser window.

NOTE: The “edit…” information after the final “/” is not needed by the IMPORTRANGE function.

Writing the IMPORTRANGE Formula

To write the IMPORTRANGE formula, select a cell and type…

=IMPORTRANGE(

The first argument is the URL copied from the previous step.  Remember, the URL must be enclosed within a set of double-quotes.

=IMPORTRANGE(“copied URL goes here”,

The second argument is the source sheet’s data location.  This information must also be enclosed in double-quotes.

If you do not mention a sheet name, the default is to select the data from the first encountered sheet in the file.

If we wished to import all data from columns A through C, we would write the following…

=IMPORTRANGE(“copied URL goes here”, “A:C”)

By leaving off the row number references we ensure we return all rows with data.  If you needed to start at a row deeper in the sheet and stop at a specific row, you could write the formula as follows.

=IMPORTRANGE(“copied URL goes here”, “A10:C100”)

Asking for Permission

If this is the first time connecting to the data source, you are likely to be prompted to ask for permission to extract the data.

Select the cell and click “Allow Access”.  The data is imported and ready for further processing.

Selecting a Specific Sheet

If the needed data is not on the first sheet, or if the target sheet is subject to movement within the workbook, it will be necessary to list the sheet’s name along with the cell range reference.

=IMPORTRANGE(“copied URL goes here”, “HR 2010!A:C”)

If we were to change the sheet name reference from “HR 2010” to “HR 2020”, we see different data returned.

Improving the Sheet Selection Step

To make this process a bit more dynamic, we could add a Data Validation drop-down list that allows the user to select the desired sheet in the source file.

For this, select a cell (ex: cell E2) and click Data -> Data Validation.

In the Data Validation dialog box, for the Criteria select “List of Items” and enter “HR 2010,HR2020” as the list items.  We will also disallow the user from entering their information by selecting “Reject Input”.

Updating the IMPORTRANGE Formula

To use the user’s sheet selection, we modify the formula to read as follows.

=IMPORTRANGE(“copied URL goes here”, E2 & “!A:C”)

While we’re at it, let’s make the formula a bit simpler by placing the source file’s URL in a cell which we will reference.  We’ll place the URL in cell E1.

=IMPORTRANGE(E1, E2 & “!A:C”)

By referencing the URL you can make your source file dynamic.  You could allow users to select from a list of files, at which point the needed file URL is located (probably by way of a lookup function.)

Testing the Dynamic Drop-down

If we change the drop-down from “HR 2010” to HR 2020” we see the new data returned based on the desired sheet.

Appending the Results of Both Sheets

Suppose we want to append/stack the results of both sheet’s data into a single results list?

Using a bit of array notation, we can combine the results of two IMPORTRANGE formulas.

With array notation, two important characters help define array dimensions:

• Semi-colon – this moves to the next row
• Comma – this moves to the next column

By writing two IMPORTRANGE formulas and connecting them with a semi-colon, we can have the results of the second formula placed below the results of the first formula.

To keep things simple, we won’t import the entire columns-worth of data, only rows 1 through 20.

=IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”)

What happened?

We can’t just enter formulas like above when creating an array of formulas, we need to enclose the formulas in a set of curly-braces.

={ IMPORTRANGE(E1, “HR 2010!A1:C20”); IMPORTRANGE(E1, “HR 2020!A1:C20”) }

Just for Fun

To demonstrate the array characters more completely, if we update the formula by replacing the semi-colon with a comma, we see the results placed side-by-side.

Why the Gap?

Notice the gap between the result sets.

This is because we have indicated that we wish to return rows 1 through 20 for each data set regardless of whether all rows contain data.

We could refine the formula to only return data from the occupied range, but this would limit our flexibility when adding rows to the source tables.  We would need to constantly refine our formulas to reflect the current dimensions of the data.

To give us this flexibility, we will combine our forces with the QUERY function.

Using QUERY to Eliminate Empty Rows

Although the QUERY function is simple to use, it does require a bit familiarity of the QUERY notation to operate properly.

I have a post about the QUERY function that you can read or watch to gain deeper insight into the QUERY function, but for now, we will just write what we need without diving too deeply into the notation’s meaning and uses.

Google Sheets QUERY Function Explained

We start by preceding the previous formula with a reference to the QUERY function and defining the source of the data.

=IMPORTRANGE(

Next, we add the query logic.

=IMPORTRANGE(

A high-level explanation of this is…

“Select all columns where the rows of the first column are not empty.”

NOTE: We can’t say col “A” when using query notation; we must reference the column’s index position within the data.  In our case, column “A” is referenced as “Col1”.

The update formula appears as follows.

=IMPORTRANGE(

The results are the two ranges of data stacked atop one another without the blank rows.

Because we captured data starting on row 1 for both IMPORTRANGE functions, we have unfortunately created duplicate headings in the data.

Bagaimana Melindungi 1 sheets agar tidak bisa diedit di Google Sheets?

Apa itu import Range?

Apa yang dimaksud dengan array formula?

Fungsi apa yang dapat digunakan di Google Sheet untuk mengambil data set dari file Google sheet yang berbeda?