1. Pendo Help Center
  2. Best practices
  3. Data Recipes

Export and automate visitor and account reports in Google Sheets

Last updated:

This article explains how to set up an automated process to extract the contents of Pendo visitor and account reports into Google Sheets through the API. This allows you to easily share product usage data with other users and teams in your organization, even if they don’t have access to Pendo. 

Notice: While you can use the solution provided in this article as a starting point to customize your own API calls and create advanced reporting within Google Sheets, this is considered custom code and is thus outside the scope of Pendo Technical Support. If additional help is needed, it needs to be evaluated by your Pendo account representative and potentially addressed through a paid engagement with our Professional Services team.

Requirements

Before continuing with the guidelines below, confirm the following:

  • API access is included in your Pendo contract. If you’re unsure, contact your Pendo account representative.
  • You have an integration key. This isn’t the key that appears in the Pendo snippet, and only Admins can create integration keys. To create an integration key in Pendo Engage, navigate to Settings > Integrations > Integration Keys and select Add Integration Key.
  • You have access to Google Sheets and the Google Apps Script extension.

Create the Google script

Follow the steps below to extract the contents of a visitor or account report directly to Google Sheets.

  1. Create a new Google Sheet.
  2. From the top menu bar, select Extensions > Apps Script to open Apps Script in a new tab.

    GoogleSheets_AppsScript.png

  3. In the new tab, your focus defaults to the Code.gs file. Delete all of the existing content, including the empty function Google adds by default, and replace it with the following text:
    /**
    * SHEET_NAME : Name of the sheet where you want to replace all data on the spreadsheet
    * REPORT_ID : ID of the Pendo visitor or account report that you want to retrieve
    * INTEGRATION_KEY : x-pendo-integration-key you obtained from the API to be passed as header
    * SPREADSHEET_ID : ID of the spreadsheet where data will be exported to sheet named SHEET_NAME
    * BASE_URL : If you access Pendo through the US instance (app.pendo.io), take no action. The default value is already correct. If your Pendo subscription is in the EU instance, change the BASE_URL value to “app.eu.pendo.io”. If your Pendo subscription is in the US1 instance, change the BASE_URL value to “app.us1.pendo.io”. If your Pendo subscription is in the JPN instance, change the BASE_URL value to “app.jpn.pendo.io”.
    */
    const SHEET_NAME = "YOUR-SHEET-NAME";
    const REPORT_ID = "YOUR-PENDO-REPORT-ID";
    const INTEGRATION_KEY = "YOUR-INTEGRATION-KEY";
    const SPREADSHEET_ID = "YOUR-SPREADSHEET-ID";
    const BASE_URL = "app.pendo.io";



    /**
    * Makes an API request to the reports endpoint on Pendo API and retrieves the CSV.
    * Data from the CSV is pre-processed, cleaned, structured, and passed to replacement in the sheet.
    * If an error occurs in the API call that breaks the code, the error is logged into Logger for investigation.
    * If the request to endpoint failed with any issues, the error is logged to Logger indicating the response error code.
    */
    function getReport() {
    // Local variables to hold the intermediate data during operations
    let params, endpoint, response, responseCode, responseObjects, dataToBeReplaced, headers, rowEntries, lineContent;
    // Parameters definition required for the API Call
    params = {
    "headers": {
    'x-pendo-integration-key': INTEGRATION_KEY,
    'content-type': "application/json"
    }
    }


    try {
    // Endpoint URL to retrieve content of report (identified based on REPORT_ID) from Pendo API

    endpoint = `https://${BASE_URL}/api/v1/report/${REPORT_ID}/results.json`;

    // Make the request to API Endpoint with required parameters, and save the response object

    response = UrlFetchApp.fetch(endpoint, params);
    // Get the response code from the latest call
    responseCode = response.getResponseCode();
    // If the response code is 200, then it was a success
    if (responseCode == 200) {
    // Get the response content as json object
    responseObjects = JSON.parse(response.getContentText());
    // Get the headers that needs to be replaced in first row of sheet later
    headers = Object.keys(responseObjects[0]);
    // Append the headers at start of replacement array (2D array)
    dataToBeReplaced = [headers]


    // Iterate through all objects in the response objects array
    responseObjects.forEach(function (object) {
    // 1D array that holds intermediate operations results for current object
    rowEntries = [];
    // Iterate through all column values for current object
    headers.forEach((header) => rowEntries.push(object[header]));
    // add current row (object) details to the replacement array
    dataToBeReplaced.push(rowEntries)
    });


    // Once the data is extracted and is in required format, replace in the named sheet
    replaceSheetData_(dataToBeReplaced);
    }
    // Otherwise, prompt the user with any failure that may occur
    else {
    Logger.log(`Something isn't right, call to endpoint failed. Status Code: ${responseCode}`)
    }


    // If an error occurs, notify the user of the error by logging into the Logger
    } catch (error) { `Something went wrong in the API call and data extraction. Error: ${error}` }
    }


    /**
    * @params {dataToBeReplaced} : 2D array of the structured/cleaned data retrieved from the Pendo Reports Endpoint
    * Opens a handle to spreadsheet and the named sheet, if anything goes wrong the process is terminated with friendly
    * messages in the Logger (to be used for further investigation on the error)
    *
    * Replaces the data in named sheet with whatever data being retrieved from the API request
    */
    function replaceSheetData_(dataToBeReplaced) {
    let workbook, worksheet;


    // Open a handle to the spreadsheet by using the SpreadSheet ID; if not found, terminate the process with friendly message
    workbook = SpreadsheetApp.openById(SPREADSHEET_ID)
    if (!workbook) {
    Logger.log("Workbook not found, the ID may not be correct");
    return;
    }


    // Open a handle to the named sheet by using the Sheet Name; if not found, terminate the process with friendly message
    worksheet = workbook.getSheetByName(SHEET_NAME);
    if (!worksheet) {
    Logger.log(`Sheet named ${SHEET_NAME} not found. Please double check the sheet name`);
    return;
    }


    // Clear whatever content is stored in the sheet at the moment (clears all data in named sheet)
    worksheet.getDataRange().clear();
    // Writes the new report data retrieved from API Response in the current sheet
    worksheet.getRange(1, 1, dataToBeReplaced.length, dataToBeReplaced[0].length).setValues(dataToBeReplaced);
    }
  4. Update the constants that are present on lines 8 through 12, which allow you to use the script with different sheets, spreadsheets, reports, and apps.
    • For const SHEET_NAME = "YOUR-SHEET-NAME", replace YOUR-SHEET-NAME with the name of the Sheet tab.

      GoogleSheets_SheetName.png

    • For const REPORT_ID = "YOUR-PENDO-REPORT-ID", replace YOUR-PENDO-REPORT-ID with the ID of the report you want to extract. To retrieve this value, navigate to the report in Pendo, then copy the ID at the end of the URL: https://app.pendo.io/s/SUBSCRIPTION-ID/visitorlist/REPORT-ID-HERE.

      Visitors_VisitorReports_ReportID.png

    • For const INTEGRATION_KEY = "YOUR-INTEGRATION-KEY", replace YOUR-INTEGRATION-KEY with a supported integration key. As described in Pendo Integration Key, only Pendo Admins can create integration keys. To extract data from Pendo, you only need Viewer access.
    • For const SPREADSHEET_ID = "YOUR-SPREADSHEET-ID", replace YOUR-SPREADSHEET-ID with your spreadsheet ID. To retrieve this value, copy the ID in the URL: https://docs.google.com/spreadsheets/d/SPREADSHEET-ID-HERE/edit#gid=0.

      GoogleSheets_SheetID.png
    • For Const BASE_URL = “app.pendo.io”, ensure that this is the URL you use to access Pendo.
      • If you access Pendo through the US instance (app.pendo.io), take no action, as the default value is already correct.
      • If your Pendo subscription is in the EU instance, change app.pendo.io to app.eu.pendo.io.
      • If your Pendo subscription is in the JPN instance, change app.pendo.io to app.jpn.pendo.io.
      • If your Pendo subscription is in the US1 instance, change app.pendo.io to app.us1.pendo.io.
  2. Select the Save icon, then select Run. This opens a dialog that requires you to authorize Google access to the data in the script. 

    GoogleSheets_AppScript_Run.png

  3. Select Review permissions, select your Google account, then select Allow.
  4. Confirm that the Google Sheet updates with the report information and that it shows the same data as the report in Pendo.

It’s important to note that the report in Google Sheets shows you the IDs of the Pages or Features, not the names of the Pages or Features. To find the ID for a specific Page or Feature, navigate to the Page or Feature Details page in Pendo and look at the URL. The Page ID or Feature ID is located at the end of the URL:

  • https://app.pendo.io/s/SUBSCRIPTION-ID/features/FEATURE-ID-HERE
  • https://app.pendo.io/s/SUBSCRIPTION-ID/visitorlist/PAGE-ID-HERE

Additionally, the order of the columns might differ when comparing against the Pendo report, but the content remains the same.

GoogleSheets_VisitorReportExport_Columns.png
Visitors_VisitorReport_Columns.png

Update report contents with a button

If desired, you can add a button to your spreadsheet so that the code runs and updates the spreadsheet with the latest report contents every time you select that button.

  1. In Google Sheets, navigate to Insert > Drawing.

    GoogleSheets_Insert_Drawing.png
  2. Choose your shape and format it to your preference, then select Save and Close.

    GoogleSheets_Drawing_SaveAndClose.png

  3. Once you add the drawing to the spreadsheet, right-click on the drawing, select the ellipsis (three dots) in the upper-right corner, and then select Assign script.

    GoogleSheets_Drawing_AssignScript.png

  4. Enter getReport, the name of the function included in the code, then select OK.

    GoogleSheets_Drawing_GetReport.png

  5. Select the button to confirm that it updates the table as expected.

Update report contents on a schedule

You can also set up automation to pull the report contents from Pendo on a recurring basis.

  1. In Google Sheets, select Extensions > Apps Script.
  2. From the left navigation menu, select Triggers (the clock icon).

    GoogleSheets_AppScript_Triggers.png
  3. In the bottom-right corner of the page, select Add Trigger.
  4. Configure the trigger to meet your needs. Each field is detailed below:
    • Choose which function to run. This should automatically detect the getReport function added in the code on step 4 in the previous section. Don’t change this selection.
    • Choose which deployment should run. This option defaults to Head. Don’t change this selection.
    • Select event source. Select Time-driven in the dropdown menu. This updates the next two dropdown options.
    • Select type of time based trigger. If you want to update the report every day, select Day timer.
    • Select time of day. Select whichever time best suits your needs. Google automatically detects your time zone.
    • Failure notification settings. We recommend Notify me immediately, but there are other options available. You can select more than one option by selecting the Plus (+) icon next to the field name.

      GoogleSheets_AppScript_Triggers_Save.png
  1. Select Save to add the trigger to run the script at the scheduled time.
Was this article helpful?
1 out of 1 found this helpful

Articles in this section