Loading Data from Pigment into Google Sheets (Gsheet)

  • 12 July 2022
  • 0 replies
  • 1387 views
Loading Data from Pigment into Google Sheets (Gsheet)
Userlevel 7
Badge +13

The Pigment Connector for Google Sheets allows you to load data into Google Sheets from Pigment views. This article will outline how to use the Load Pigment View functionality of the connector, there are other articles on how to install and also how to push data from Google Sheets into Pigment. 

 

Table of Contents

Check out the Google Sheets Integration Micro-learning series in the Pigment Academy!

 

To install the Pigment’s Connector for Google Sheets check out this article on how to install Pigment’s Connector or you can simply click this link to go directly to the Google Workspace Marketplace.

 

 

Overview

The Pigment Connector for Google Sheets allows you to pull data into a Google sheet from any view in your model. Once data is pulled into sheet, you can easily click a button to refresh your data.  

 

 

Once data is pulled into a sheet, it will no longer automatically update if changes are made in the application.  Think of it as an export from your application.  However, you can pull in the latest data at any time.  

 

Permissions

Users that have the Display Application Permission will be able to pull data from Pigment into Google Sheets.  Access Rights are applied so they will only be able to extract the data they have Read or Write access to. 

 

 

How to pull data into Google Sheets

 

Data is pulled into Google Sheets through Connections. Connections allow you to designation which views in Pigment you want to bring into Google Sheets.  Each connection will be in alignment with a single sheet and you can only have on connection per sheet..

 

1. Activate the connector by navigating to Extensions > Pigment Connector > Open Pigment Sidebar

 

2. Click Login and enter your Pigment credentials.  After you login, you can close the popup.

If you do not see the Pigment login screen activate.  Please follow your browser specific instructions to enable pop-ups.

 

3. To pull in data, select Load Pigment View.

 

4.Select the Sheet you wish to import data into.  

Click +Create  Connection 

You can also select the + symbol to Create a new connection

 

5. Set the Connector configurations to define what data should be part of the connection and where the data should go in Google Sheets.     Once established, click Create

 

 

Connector configuration options

 

Source 

This section is dedicated to identifying the data in Pigment.

Application : Choose the application that you wish to pull data from

You will see all applications within the workspace that have shared blocks.  Even applications you do not have access to.  You will not be able to pull data from those applications outside of the shared blocks. 

Block

Select the Block you wish to import data from.

View

Select the view you wish to import. 

If the view changes, when you refresh your data all changes will also be reflected. Because of this we recommend saving a specific view and not importing from the default.   

 

Target

This area defines the location in google sheets where the data will be imported into.

Sheet - 

Select the sheet that you want data to be imported into.  You can only have one connection per sheet.

Cell

This is the cell in Google sheets where the data will start.  It will be the top left of all imported data.  By default it is set to A1.  However, if you have a cell selected when you start creating a connection that value will be selected.

 

 

 

Working with Connections 

Once a connection has been established you can refresh data, adjust page selectors, edit options and make duplicates.

 

Viewing Connections

 

At the bottom of the connector sidebar, you can click View all connections to see all the created connections for that spreadsheet.  

From the All connections screen, you can identify all the connections for this Spreadsheet.  

 

The Connection name will match the name of the sheet that it is established on.  If you change the name of the sheet, the connection name will update the next time the connection is refreshed. 

 

Under the Connection name, you’ll see the name of the Pigment application. 

 

The block symbol and name will be located below that.  

 

From the All Connections screen, you can select Pull All to refresh the data from all connections.  You can also select the pull icon next to separate connections to run them individually.

 

You can click on the Connection name to open that connection or select the ellipsis menu to view more options

 

 

 

Adjusting Page Selectors 

 

Within a connection, you can adjust the Page Selectors to define which data you would like to view. Under Pages Settings, you’ll see any dimensions that were in Pages from your view.  You can adjust these to the desired date you wish to see.  After adjusting the Page Selector, click the Pull button at the top to refresh the data to match the new selection..   

 

Access Rights are still applied within the Google Sheet Connector.  When working with Pages, users will be able to see all options.  However, they will not be able to import data that they do not have access to within Pigment. 

For example, if a user does not have access to US data.  They will still see the US as a Page Selector option but the data will not come through.  

 

Identifying Active connections on a sheet

 

When on a Sheet, you can click the Active Sheet Connection button to switch over to any established connections for that Sheet.  If you are on a different sheet and run a connection, you will be prompted to switch to the active Sheet.

 

If a Sheet with an active connection is deleted, you will receive an error message. You can edit the connection and apply it to a new sheet. 

 

 

Adjusting Connections

 

Select the more options icon next to a connection to edit, duplicate, reload view configuration or delete a connection. 

 

Edit connection - This allows you to adjust the Application, Block, View, and Target specifics.

Duplicate connection - This is used to replicate the configuration settings.  When you duplicate a connection you need to specify a new sheet because you can only have one active connection per sheet.

Reload View configuration - This option will refresh you connection with any changes that might have been made to the view in Pigment. 

Delete Connection - This will remove a connection.

 

While it is possible to Duplicate a connection assign it to a new sheet, it is not possible to duplicate an entire workbook and copy connections over or set a connection to a different workbook. 


This topic has been closed for comments