How to connect Google Sheets to Pyramid
Pyramid can model and blend numerous complex data sources for basic or advanced analysis.
The quick tutorial below shows how you can use Pyramid to access and import data stored in a Google Sheet, one of the most common and convenient data sources.
Part 1 – Preparing your Google Sheet URL
Navigate to your Google sheet.
Click the “Share” button in the top right-hand corner.
Click the “Get shareable link” in the top right-hand corner of the pop-up. The pop-up will change too.
Click the dropdown that reads “Anyone with this link can view.” Then select “More…” and confirm “On - Anyone with the link” is selected. (The description text should read “Anyone who has the link can access. No sign-in required.”) Then click the blue “Save” button.
Click the “Copy link” button.
Paste the link in any text editor so we can make a small adjustment. The link should look similar to this:
Replace “/edit?usp=sharing” with “/export?format=xlsx”. The revised link should look like this:
Keep this updated link handy, we will use this link in part 2.
Part 2 – Importing the model into Pyramid
Log into Pyramid. Ensure you have the latest version. If you still need to download the application, visit the Pyramid Customer Portal.
Start a new model by clicking the yellow Model icon in the quick navigation panel.
Click the “Advanced Data Flow” button.
Confirm the “Sources” ribbon on the far-left side of the screen is selected. Drag and drop the Excel source icon into the Data Flow panel.
A “Properties” panel will appear on the far-right side of the screen. Find the “Excel File Source” option box inside that panel.
Change the dropdown from “Uploaded File” to “URL”.
Paste your Google Sheet URL (from part 1) into the “Set URL” text box and click “OK”.
All available sheets will now become accessible for modeling.
Part 3 – Building a Model (optional)
Under “Table Selection” on the right-hand side of the screen, select the individual sheets you want to use in your model, or just choose “Select All”. Next, click “Add Select Nodes”.
A “Select” node will appear for each sheet.
Once the sheet has loaded you can further modify your model using the visual modeler.
We now need to tell Pyramid where to store the data from the source.
Select the “Targets” option from the ribbon on the far left.
You can select from among more than a dozen model targets.
Click on the “In Memory” node to reveal the “Properties” panel on the far-right side of the screen.
Click on the “New Database” radio button. Enter a name, such as “Google Sheets Tutorial”. Next, click “Connect All”.
Click the “Save” icon in the center panel above the canvas. This will open a Save dialogue box. Select “My Content” and enter a name for the model in the Name field (such as “Google Sheets Tutorial”), and click “Save”.
Select the Data Model Tab. Next, click the Pyramid icon in the top-left corner. Last, confirm “Launch Data Discovery” is selected and click and click the “Data Flow & Model” button. You are now ready to begin data discovery on your Google Sheets data.
Pyramid is a power package that out of the box connects to dozens of data sources like Facebook, Twitter, Google Analytics, Snowflake to name a few. For a full list of ETLs supported click here.
Comment below if there are any data sources that you would like to see added incoming updates of the application.
Is this still relevant or have there been changes to the process for this? Right now, the Excel source node doesn't work with the url. The error I see is 'failed to read null' with no further details. I tested to see if it's permissions but the link works fine and is set up the same way as above.
Hi Bhargav Pandravada
It's a known issue that should be fixed in the next version.
(Current version 2020.04.007)
Is there a way to connect a restricted access google sheet? Some of the data I have I don't want to make available to anyone on the internet.
- 3 Likes
- 2 yrs agoLast active
- 5 Following