Jump to content
  • Write back to Google Sheets from Spotfire using Python Data Function


    This article explains how to use Python Data Functions to write data back to Google Sheets from Spotfire.

    Introduction

    As the off-premise storage solutions grow, the need to read and write data to cloud systems is becoming more and more essential.  Many companies are enforcing the use of Cloud storage in preference to storing files on employees hard drives, and embracing the collaboration options that these vendors provide.

    Spotfire Community makes available Connectors to Google Sheets, Microsoft SharePoint Online, Amazon S3 and Dropbox and also has the Cloud Drive Export which allows writing data to these cloud storage platforms.

    However, there are other cases crop up that may require:

    • More fine grained control of what data is written
    • Different platforms or data storage mechanisms
    • Accessing a 3rd party API
    • Python libraries that require OAuth or OpenID authentication

    In this example, we are going to show how to write back data from within Spotfire to Google Sheets using a Python Data Function.  This will demonstrate how to:

    • Setup a Python Data Function
    • Configure the required Google Authentication, which is similar to other cloud vendors
    • Use the Google Python libraries
    • Choose which data from Spotfire to pass to the Data Function 

    Although this example is specific to Google Sheets, the same process goes when writing back to any other Google Cloud Platform system such as Google Big Query, and the same overall steps could be used as a guide for other scenarios.

    Setup Google Cloud Platform Authentication

    To set up the authentication, we need to create a project and create a service account that can perform calls to the Google Sheets API

    Create a new project

    A Google Cloud Platform Project consists of a set of users, APIs, billing, authentication, resources, and more. 

    Go to console.cloud.google.com, sign in with your Google account, and create a new project by clicking the top dropdown.

    image.png.570c4c2260bbce8a7d363a4ca03764f4.png

     

    image.png.4ee2f0b8a940a94eb161e568d2c548de.png

     

    Enable Google Sheets and Google Drive API

    An API is Application Programming Interface. Google Cloud Platform has many of these, so we need to enable for this case the Google Drive and Google Sheets API.

    From the main menu on the left, choose APIs & Services, then select Enabled APIs & services and search for Google Sheets and Google Drive APIs

    image.png.15c9868ec39804930a35a4046331c51c.png

     

    image.png.5596a8156fd7ae567580127990592706.png

     

    Setup a service account

    A service account is a special type of account intended to represent a non-human user that needs to authenticate and be authorized to access Googles APIs. Go to IAM & Admin to setup a service account. If not already done so, create a service account that will be working with this API through the + create credentials shortcut. You can always go to IAM & Admin - Service Accounts from the main menu and create one. Be careful the role given for this account. The Owner role allows access to most Google Cloud resources.

    image.thumb.png.29b0de5637f7637406e984fa3fef6b9d.png

    image.png.a7146ccb5964b389adfded82f65b8b5a.png

     

    Create authentication keys

    Google Cloud APIs use the OAuth 2.0 protocol for authenticating both user accounts and service accounts. The OAuth 2.0 authentication process determines both the principal and the application. Most Google Cloud APIs also support anonymous access to public data using a GCP service account key. This key is use by the applications to make authorized API calls but in this exercise we will be using authentication keys. 

    Form IAM & Admin - service Accounts, click on the service account you created and go to the keys tab, or go directly to Manage Keys from the Service account menu.  Create a new JSON key to download a json file containing the credentials. We are going to need the contents of this file later. Keep this service account password private, personal and in a safe place!

    image.thumb.png.63ffb468513e12270858741544ccb8d1.png

     

    A service account JSON key file looks like this:

    {
      "type": "service_account",
      "project_id": "spotfire-cloud-writeback",
      "private_key_id": "jfielwssasdf99asdf02345d03453459dlldflsd",
      "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQC7sd0q8HFh+I2T\nfpEN0pTMVRyTGWteZ/cSZR0uhMwIEUarlNWown9Y3QjLgxOYCkVGMO3726ewm3gm\nWxu4RZdDP984p6WQAsgPKbed+X3ahmrbmtxRBAEh5JZJgTeOl8wP/GG3/jsyEAvL\nr1WTO3Sk/ysRraOYq0KkMvI0VKTbOayPHuJ4QEe4AKlwTqZdYYtPU7Eq3+h+XNgY\nhB3L51mVe5f15/l/yfeS2YaAMiQqHypTDC6fvXPSkCBrv2A\nSssy0VNATwKBgEg5KZysUDKPinYMS950HBdqAaCB2WPd64Dh97hu05O0EUuqI0W4\nmwYhTJOaDqcmYiaqWc6d65nkvs/fjgquf9EmI+GTusT4uB/pM3XMKNQR6edNfNHE\nDT07cq3JUhNyMK+j2oXr+BLscqBhMK7vxUWNztbETDEDteorK3Y9dLAtAoGAauHh\n45CQNspuDixTiL4s3eI29yWOw03MNVFRA9sqktRh/Rwn4boCpb3DHkbg9gTzVVno\n8gv6KPqc35KrHsYxN4sV3oQuwvYhjTsdQp3wq3+qSGdMdqIC5Lu1rWpEzq94sm+W\n8VAzS6v1jXhwgjY2Btrs9Q9AG27s9klx6IAyuZUCgYB7GYPp08b+86wDoPggTNRi\nweE5eylpc+YBLFPZ0DCErjMyOctFFrHk8l+8T8OQzgpXO3udw3NYRH4qeYd3+ZN3\nSnuvaXjNzdht2olixeqqcadHX5CRngkmpKIT/EexkI+uLhvVgbAv/te+dklAbaSI\n5DxQKriHclJOAlui0XyNEg==\n-----END PRIVATE KEY-----\n",
      "client_email": "example@spotfire-cloud-writeback.iam.gserviceaccount.com",
      "client_id": "112233445566778899001",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/cacaca%40spotfire-cloud-writeback.iam.gserviceaccount.com"
    }
     

    Share a Google Sheet to GCP Service Account

    All is left from the Google side is to share the google sheet to the service account email. The service account email can be found on the Service Account JSON Key file. This Service Account looks like this: example@spotfire-cloud-writeback.iam.gserviceaccount.com

    image.thumb.png.606d0653b65d6bbf3ecab04488f12969.png

    Configure Spotfire to Write back to Google Sheets

    Now that we have the authentication and Google Sheets in place, the final step is to create a Data Function to send data to Google Sheets. You can leave Google Sheets open and see how the data flows right after the data function executes. No need to refresh the sheet every time. 

    Install python packages

    The required packages for the data function to work is oath2client and gspread. oath2client is a client library for OAuth2 delegates user authentication to the service that hosts a user account, allowing Spotfire to use the Google Sheets API. The gspread python package is a simple interface for working with Google Sheets

    These packages can be installed using Python Tools from TIBCO Spotfire client tools menu.

    image.png.0fe16bbda5d8d8d0cd4a008d01d6de90.png

     

    Register a new data function

    Data functions are the Spotfire way of letting advanced analysts, statisticians or mathematicians enhance Spotfire by creating scripts that can perform pretty much any type of calculation and returning the results to a Spotfire analysis

    In TIBCO Spotfire, on the menu bar, select Tools > Register data functions, select Python script and paste the code below to the script tab. Run the data function and check if the data function does not throw any errors. Check for errors with the notification icon. If there is some trouble with the loaded data, with a script or data function, or with a calculation in the analysis, a warning indication is displayed on the notifications icon on the menu bar.

    image.png.c04f6d74ca5aedf443eb6c9879ecaed7.png

    Edit Data Function

    The rest of the script performs authentication and inserts a row of data. Many other operation can be done with different methods but to keep things simple, the code has the minimal number of lines of code required to write back to Google sheets.

    Do not continue to edit the code but rather close the Register Data Function dialog. Click No to embed the data function in the analysis. You can always save it to the library later.

    image.png.07067bfe9a1b26af4fccd615e0d98c7f.png

     

     

    From Data > Data function properties select the script and edit by pasting the rest of the code. Just replace the keys variable with the content of the JSON key file

    from import gspread
    from oauth2client.service_account import ServiceAccountCredentials
    import json
    
    scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
    ]
    
    keys = {
      "type": "service_account",
      "project_id": "spotfire-cloud-writeback",
      "private_key_id": "463a32...",
      "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvwxxx...",
      "client_email": "pythongsheets@spotfire-cloud-writeback.iam.gserviceaccount.com",
      "client_id": "112223334445556667778",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/pythongsheets%40spotfire-cloud-writeback.iam.gserviceaccount.com"
    }
    import datetime
    tstamp = str(datetime.datetime.now())
    
    credentials = ServiceAccountCredentials.from_json_keyfile_dict(keys, scopes) 
    connection = gspread.authorize(credentials) 
    workbook = connection.open("spotfire comments") 
    sheet1 = workbook.get_worksheet(0) 
    sheet1.append_rows(values=[[tstamp, comment, value]])
     

    Configure Data Function parameters

    The script does not return any values at this point but input is required. The comment and value variables can be mapped to document properties

    image.png.4f7a4d59ed029e34b1a5108722899c1f.png

     

    Reading data from Google Sheets

    The recommended way to read back from google sheets would be to use the Google Sheets Connector available in the Spotfire Community Exchange.  However, as an example of how to do this with a Python Data Function, you can use the following script to fetch the data as JSON or a Data Frame, which can be output to a document property or a Data Table respectively.

    As before, this technique can be used with other Cloud storage platforms, databases or APIs.

    import gspread
    from oauth2client.service_account import ServiceAccountCredentials
    import json
    import pandas as pd
    import numpy as np
    
    scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
    ]
    
    keys = {
      "type": "service_account",
      "project_id": "spotfire-cloud-writeback",
      "private_key_id": "463a3246b97bd9f364d9efb7d334df3a9fbf195d",
      "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggS...FAeAQ==\n-----END PRIVATE KEY-----\n",
      "client_email": "pythongsheets@spotfire-cloud-writeback.iam.gserviceaccount.com",
      "client_id": "112223344556677889900",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robo...teback.iam.gserviceaccount.com"
    }
    
    credentials = ServiceAccountCredentials.from_json_keyfile_dict(keys, scopes) 
    connection = gspread.authorize(credentials) 
    workbook = connection.open("spotfire comments") 
    worksheet = workbook.get_worksheet(0) 
    
    data = worksheet.get_all_values()
    
    #return as json 
    #output = (json.dumps(data, indent=2, sort_keys=False))
    
    #return as data frame
    output       = pd.DataFrame(np.array(data),columns=['date', 'comment','rating'])
     

    See also

    References


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...