Following a few demos of this trick in hangout with several clients here is an post on how to combine documents from different views and display them in an AwesomeTable:
Objective:
Display documents from different views in a single view.
Note: The different views can come from different libraries.
Example:
My company has different policies managed by AODocs from different departments (HR, Finance, Engineering).
They are not in the same library or in the same class which makes it difficult to have a single view or repository where my company users can find all the policies of the company. Also the documents might not share all the same custom properties.
I want to provide my users with a simple view to consult and access all the policies of the company who are always up to date.
Solution in 3 steps:
For each view, create a synchronized Google Sheet. |
Consolidate all the sheets in one main sheet. |
Create an AwesomeTable view of the consolidated sheet |
Here is the architecture of the tools we are going to build:
Create a synced Google Sheet for each view
Following the AwesomeTable connector User guide configure as many spreadsheet as you have of views you want to configure.
Consolidate all the sheets in one
1.Create a new Google sheet: “Consolidated Sheet”.
2. In this Google Sheet, create one sheet “ViewX” per Google Sheet you created at the previous step.
Use the formula:
|
A |
B |
C |
1 |
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/<idOfYourSpreadsheet>/edit", "AODocs!A1:K") |
... |
.. |
2 |
... |
... |
.. |
Note:
- https://docs.google.com/spreadsheets/d/<idOfYourSpreadsheet>/edit is the link of the spreadsheet which synchronizes the views, adapt it with the ID of your spreadsheet.
- A1:K is the range of the sheet which contains the data you want to consolidate. Adapt this range to your view.
You will need to authorize the sheet to pull data from the other sheets.
The ImportRange formula creates a dynamic synchronization with the others sheets which are themselves synchronized hourly with AODocs.
Important: Delete the blank lines at the bottom of each view.
3. Create a new “Consolidation” Sheet.
This sheet will be used by AwesomeTable in the next step, so the first Row has to be the name of the column and the second row is used for filters.
4. Select which columns from the other sheets you want to consolidate. They do not have to be all the columns you retrieved in the other sheets.
For instance, you want to consolidate the “Title” of all the policies in the column A, and the “Effective Date” in the Column B, your sheet will look like this:
|
A |
B |
C |
1 |
Title |
Effective Date |
... |
2 |
StringFilter |
DateFilter |
... |
3 |
={View1!B3:B,View2!B3:B;View3!B3:B} |
={View1!X3:X,View2!Y3:Y;View3!Z3:Z} |
... |
Using the Formula ={Range1,Range2,RangeX,...} concatenates different arrays from the other sheets in one.
Note: In this example above the property “Effective Date” is in different columns in each sheet, in the column X in the sheet View1, in the column Y in the sheet View2 and in the column Z in the sheet View3. You need to adapt the column according to its location in the different sheets.
5. Repeat the operation until you have consolidated all the columns you want to display.
6. Setup the filters in the row 2 according to the AwesomeTable documentation.
Create an AwesomeTable View from the consolidated view
1. Go to AwesomeTable.
2. Create a new View from a blank or template.
3. Select the Google Sheet “Consolidated Sheet” and the corresponding range of data.
4.Configure the AwesomeTable view to your liking.
5. Publish your AwesomeTable where you want (Website, Google Site, Intranet,..).