> For the complete documentation index, see [llms.txt](https://delibdocs.gitbook.io/welcome/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://delibdocs.gitbook.io/welcome/integrations-and-playbooks/google-looker-studio/google-looker-studio.md).

# Google Looker Studio

Create a realtime data dashboard with Citizen Space and Looker Studio

Looker Studio, formerly Google Data Studio, is a powerful online tool that enables you to convert data into customisable reports and dashboards. In this example, we'll show you how to import your data from Citizen Space to create a real time overview dashboard for your site using the Citizen Space API's.

{% hint style="info" %}
To get started you will need to be a Site Admin to create an API key on Citizen Space. You will also need access to Looker Studio and Google Sheets.
{% endhint %}

### Quick steps

1. [Create an API key on your Citizen Space](#create-an-api-key)
2. [Create a new Google Sheet](#create-a-google-sheet)
3. [Enable and open extensions App Script](#enable-and-open-extensions-app-script)
4. [Grab the quick code below and copy in your details](#code-snippet-to-pull-in-our-data)
5. [Run the script to import data to your Google Sheet](#run-the-script-to-import-data-into-your-google-sheet)
6. [Open Looker Studio and select your Google Sheet as the data source](#open-looker-studio-and-select-your-sheet-as-the-data-source)
7. [Create your dashboard](#create-your-dashboard)

### Create an API key

Creating an API key is a fundamental step in securely connecting Citizen Space to external services. An API key serves as a unique identifier that authenticates your application ensuring that only authorised users can access your data. If you haven't already, the steps to do this are highlighted in the articles below:

{% content-ref url="/pages/r5YhSasb66L11lmkQlh7" %}
[Generating API keys](/welcome/citizen-space/data-api/generating-api-keys.md)
{% endcontent-ref %}

### Create a Google Sheet

To build dashboards in Looker Studio, we first need to retrieve our data from Citizen Space. It is possible to directly pull data into Looker Studio via a custom connection. However, in this example, we're not going to be pulling a PII or respondent information - just site information about activities and response rates, so we're going to pull it into a Google Sheet first. To do this, we need to create and name a new sheet: File > New > Spreadsheet.

<figure><img src="https://2022331909-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FlJLWY1pa4hJpykxLVhx5%2Fuploads%2FqMXqBhFTP1JZj97dEllS%2FGoogle%20Sheet%20New.png?alt=media&amp;token=316c370d-ceab-45ce-af6a-7e1d7d1d4813" alt=""><figcaption><p>Add a new Sheet</p></figcaption></figure>

### Enable and open extensions App Script

If you've used Looker Studio before, you likely  already know what App Script is. If you're unfamiliar, Google Apps Script is a scripting platform developed by Google for light-weight application development in the Google Workspace platform. In this example we use it to run a script to call our Citizen Space API's.

To open up App Script go Extensions > Apps Script.

<figure><img src="https://2022331909-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FlJLWY1pa4hJpykxLVhx5%2Fuploads%2F8MQrgoIz3towW1spw4Z2%2FApp%20Script.png?alt=media&amp;token=e23166a3-8aaf-4c4c-a22b-12d2a63ae6ff" alt=""><figcaption><p>Open Apps Script</p></figcaption></figure>

### Code snippet to pull in our data

Once you've opened up Apps Script you can paste in the code below. Simply replace the placeholders with your Citizen Space URL, Key and secret and Looker Studio will do the rest.<br>

Once saved and run, it should populate your Google Sheet with columns for:    &#x20;

| uid         | workspace\_title    | workspace\_id            |
| ----------- | ------------------- | ------------------------ |
| title       | link\_text          | thankyou\_message        |
| state       | question\_numbering | email\_thankyou\_message |
| private     | linear              | has\_skip\_logic         |
| start\_date | body                | response\_count          |
| end\_date   | factbank\_heading   | factbank                 |
| path        |                     |                          |

To pull this data, it uses the Citizen Space API's to make a series of calls. Firstly, it makes a call the the **List Activities** endpoint to get information about what activities are on the site. It then uses the data from that initial call to then use the **Inspect Survey** endpoint, which gets more detailed information about the activities themselves. It uses the **Workspaces** endpoint to match the what workspace the activity belongs to. And **f**inally, it uses the **List Response** endpoint to get response numbers for each of the activities.

{% code overflow="wrap" lineNumbers="true" fullWidth="false" %}

```javascript
// Don't forget to paste in your Citizen Space URL, Key and Secret on line 11, 12, 16

function encodeApiCredentials(key, secret) {
  const credentials = key + ":" + secret;
  return Utilities.base64Encode(credentials);
}

function fetchData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  
  // Replace this with your URL and make sure it doesn't have a /
  const CITIZEN_SPACE_URL = "https://your.citizenspace.com";
  
  // Replace with your API key and secret
  const API_KEY = "your_key_here";
  const API_SECRET = "you_secret_here";
  
  // Encode the credentials
  const encodedCredentials = encodeApiCredentials(API_KEY, API_SECRET);
  const headers = {
    "Authorization": "Basic " + encodedCredentials
  };
  const options = {
    "method": "GET",
    "headers": headers
  };
  try {
    // Clear existing data
    sheet.clear();
    // Set up headers - now including private status and workspace information
    const columnHeaders = [
      'uid', 'title', 'state', 'private', 'start_date', 'end_date',
      'workspace_id', 'workspace_title', 'response_count', 'link_text',
      'question_numbering', 'linear', 'body', 'factbank_heading', 'factbank',
      'thankyou_message', 'email_thankyou_message', 'has_skip_logic'
    ];
    sheet.appendRow(columnHeaders);
    // Fetch workspaces first and create a mapping
    const workspacesResponse = UrlFetchApp.fetch(CITIZEN_SPACE_URL + "/api/1/workspaces", options);
    const workspaces = JSON.parse(workspacesResponse.getContentText());
    // Create a mapping of workspace UIDs to their details
    const workspaceMap = {};
    workspaces.forEach(function(workspace) {
      workspaceMap[workspace.uid] = {
        title: workspace.title,
        id: workspace.id
      };
    });
    // Fetch activities list
    const activitiesResponse = UrlFetchApp.fetch(CITIZEN_SPACE_URL + "/api/1/activities", options);
    const activities = JSON.parse(activitiesResponse.getContentText());
    // Process each activity
    activities.forEach(function(activity) {
      try {
        // Get survey details
        const surveyResponse = UrlFetchApp.fetch(
          CITIZEN_SPACE_URL + "/api/1/activities/" + activity.uid + "/survey",
          options
        );
        const surveyData = JSON.parse(surveyResponse.getContentText());
        // Get response count
        const responsesResponse = UrlFetchApp.fetch(
          CITIZEN_SPACE_URL + "/api/1/activities/" + activity.uid + "/responses",
          options
        );
        const responsesData = JSON.parse(responsesResponse.getContentText());
        const responseCount = responsesData.length;
        // Get workspace information
        const workspaceInfo = workspaceMap[activity.workspace_uid] || { title: 'Unknown', id: 'Unknown' };
        // Combine data
        const rowData = [
          activity.uid,
          activity.title,
          activity.state,
          activity.private,
          activity.start_date,
          activity.end_date,
          workspaceInfo.id,
          workspaceInfo.title,
          responseCount,
          surveyData.link_text,
          surveyData.question_numbering,
          surveyData.linear,
          surveyData.body,
          surveyData.factbank_heading,
          surveyData.factbank,
          surveyData.thankyou_message,
          surveyData.email_thankyou_message,
          surveyData.has_skip_logic
        ];
        sheet.appendRow(rowData);
      } catch (e) {
        Logger.log('Error processing activity ' + activity.uid + ': ' + e.toString());
      }
    });
  } catch (e) {
    Logger.log('Error fetching data: ' + e.toString());
    throw new Error('Failed to fetch data: ' + e.message);
  }
}
```

{% endcode %}

### Run the script to import data into your Google Sheet

Once you've replaced the placeholder "YOUR API KEY BASE64" and "YourCitizenSpaceURL" with your details, simply hit the Run button. If your script is successful you should see a notification "Execution completed" in the Execution log.

<figure><img src="https://2022331909-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FlJLWY1pa4hJpykxLVhx5%2Fuploads%2Fk1NHFmmBY5bZkbprRNeH%2FRun%20Script.png?alt=media&amp;token=43dd2e7a-a57f-469f-a54f-04b5f60921c5" alt=""><figcaption><p>Run script</p></figcaption></figure>

### Open Looker Studio and select your sheet as the data source

If the script has worked successfully you should have a sheet populated with data that is separated into the fields mentioned in the steps above. For the most part, that's the hard work done! Now it's time to open up Looker Studio and select "Connect to Data".&#x20;

At this stage you should be able to simply select your Google Sheet as the data source to bring in all the site wide data from Citizen Space.

<figure><img src="https://2022331909-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FlJLWY1pa4hJpykxLVhx5%2Fuploads%2FrVKf3WIL8OMFHatpOPiO%2FConnect%20to%20Data.png?alt=media&amp;token=74386453-d456-4863-92bd-c3b450f966d2" alt=""><figcaption><p>Connect to data</p></figcaption></figure>

### Create your dashboard

Creating dashboards in Looker Studio is highly intuitive and it is as simple as selecting the data fields and charts you would like and dragging them onto the canvas like the example below.

<figure><img src="https://2022331909-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FlJLWY1pa4hJpykxLVhx5%2Fuploads%2FyLwEZWwdghV0yYIk81p0%2FLooker%20Studio%20Dashboard.png?alt=media&amp;token=e142799b-4999-4786-a1ea-de58ec7dc8cf" alt=""><figcaption></figcaption></figure>