Export data to Google Sheets

If you want to use your nodegoat data in a spreadsheet application, you can export your data as a CSV file and open this file in your application of choice. This guide describes a more dynamic approach: use the Google Apps Scrips extension to pull in data from your nodegoat environment into a Google Sheets document. This approach removes the step of generating CSV files from within nodegoat and importing these CSV files into the spreadsheet application. An additional benefit of this workflow is the ability to merge multiple values into single cells (cf. 'Exporting 'multi' Elements').

In this guide we will first show how Objects of a single Type can be pulled in and listed in a sheet. We will then show how cross-referenced categories can be fetched to generate a pie chart. Finally we will share a script that allows you to dynamically pull in all selected data from a specific nodegoat Project. We use a dataset of nineteenth century European statues that is a subset of a data collection of the Study Platform on Interlocking Nationalisms of the University of Amsterdam.

To make this work, first configure the API of your research environment in such a way that you can run queries to fetch the data you want to use. You should have enabled at least one project in the API settings, and you should have configured at least one active client and one active user who has access to the enabled project.

The identifiers of Objects, Object Types, Scopes, or Filters (and any other nodegoat element) are shown under the name of the element when you edit or view the element. You will use these identifiers to construct the API queries.

If you do not have access to the API module that can be accessed via 'Management', please contact the administrator of your nodegoat environment or send a message to support@nodegoat.net.

We recommend to first run your query as a cURL command to confirm that the requested data is correctly returned. For example:

curl https://nodegoat.io/data/type/[Your Object Type ID]/object&limit=1 -H 'Authorization: Bearer [Your Passkey]'

Use Apps Script to fetch nodegoat Objects of one Type  

Create a new Google Sheets document and go to 'Extensions > Apps Script'. Rename your Apps Script project to 'Fetch Data from the nodegoat API'. By default a 'run' function is displayed in the script editor. Rename this 'run' function to 'fetch' and add the following lines to this function:

// Select the active sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Specify the options that are sent as part of the request to the nodegoat API
const options = {
 'method' : 'GET',
 'contentType' : 'application/json',
  'headers' : {
    'Authorization' : 'Bearer [Your Passkey]'
  }
 };

// Receive the response of the request and parse it
const response = JSON.parse(UrlFetchApp.fetch('https://nodegoat.io/project/[Your Project ID]/data/type/[Your Object Type ID]/object', options));

// Go to the position in the response that contains the data
const data = response['data']['objects'];

Next, you iterate over the returned Objects and create an array that contains an array for every Object that will be displayed as a row.

// Create an array that will contain an array for every returned Object  
let output = [];

// Add the column heads to the output array
output.push(['Object Name','Kind of Statue']);

// Iterate over the returned Objects
for (const object_id in data) {

  // Go to the position of the selected Object
  const object = data[object_id];

  // Set the name of the Object
  const object_name = object['object']['object_name'];

  // Join a 'multi' Object Description into a single string of multiple values separated by a comma
  const kind_of_statue = (object['object_definitions']['240'] ? object['object_definitions']['240']['object_definition_value'].join(', ') : '');
    
  // Add the Object as a row to the output array
  output.push([object_name,kind_of_statue]);
}

Finally you write this data to the active sheet.

// Remove any previously entered data in the active sheet
sheet.getRange(1,1,5000,60).clearContent();

// Output the rows of the output array in the active sheet
sheet.getRange(1,1,output.length,2).setValues(output);
Click here to display the complete Apps Script file.
function fetch() {

  // Select the active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Specify the options that are sent as part of the request to the nodegoat API
  const options = {
   'method' : 'GET',
   'contentType' : 'application/json',
   'headers' : {
     'Authorization' : 'Bearer [Your Passkey]'
   }
  };

  // Receive the response of the request and parse it
  const response = JSON.parse(UrlFetchApp.fetch(https://nodegoat.io/project/[Your Project ID]/data/type/[Your Object Type ID]/object', options));

  // Go to the position in the response that contains the data
  const data = response['data']['objects'];

  // Create an array that will contain an array for every returned Object  
  let output = [];

  // Add the column heads to the output array
  output.push(['Object Name','Kind of Statue']);

  // Iterate over the returned Objects
  for (const object_id in data) {

    // Go to the position of the selected Object
    const object = data[object_id];

    // Set the name of the Object
    const object_name = object['object']['object_name'];

    // Join a 'multi' Object Description into a single string of multiple values separated by a comma
    const kind_of_statue = (object['object_definitions']['240'] ? object['object_definitions']['240']['object_definition_value'].join(', ') : '');
    
    // Add the Object as a row to the output array
    output.push([object_name,kind_of_statue]);
  }

  // Remove any previously entered data in the active sheet
  sheet.getRange(1,1,5000,60).clearContent();

  // Output the rows of the output array in the active sheet
  sheet.getRange(1,1,output.length,2).setValues(output);
}

To execute this script, click the 'Save project' button and click the 'Run' button. When you first run an Apps Script, you need to authorise the project. In the 'Authorization required' popup that opens you can click 'Review permissions', select your Google account, and click 'Advanced' when you are notified that Google hasn’t verified this app. Click 'Go to Fetch Data from nodegoat API (unsafe)' and click the 'Allow' button.

Use Apps Script to fetch Cross-Referenced Categories  

The dataset of statues mentioned above has been classified by means of a Classification called 'Reference Period'. This classification contains the categories 'pre-500', '500-1000', '1000-1250', '1250-1450', '1450-1550', '1550-1650', '1650-1800', and 'post-1800', and refers to the portrayed subject of a statue. To get a visual representation of the proportion of each of these categories based on the complete set of statues, you could decide to generate a pie chart.

To do this, we use the same approach as described above only this time we fetch both the Objects of the Type 'Statue' as well as all the Categories of the Classification 'Reference Period'. Once we have fetched both sets of data we iterate over the categories first to create the following Object:

{ 
 '136519': { name: 'pre-500', amount: 0 },
 '136520': { name: '500-1000', amount: 0 },
 '136521': { name: '1000-1250', amount: 0 },
 '136522': { name: '1250-1450', amount: 0 },
 '136523': { name: '1450-1550', amount: 0 },
 '136524': { name: '1550-1650', amount: 0 },
 '136525': { name: '1650-1800', amount: 0 },
 '136526': { name: 'post-1800', amount: 0 } 
}

We then iterate over all the returned Objects of statues and increase the amount of each referenced category. This process leads to the following result:

{ 
 '136519': { name: 'pre-500', amount: 27 },
 '136520': { name: '500-1000', amount: 53 },
 '136521': { name: '1000-1250', amount: 38 },
 '136522': { name: '1250-1450', amount: 103 },
 '136523': { name: '1450-1550', amount: 86 },
 '136524': { name: '1550-1650', amount: 119 },
 '136525': { name: '1650-1800', amount: 253 },
 '136526': { name: 'post-1800', amount: 864 }
}

The next step is to iterate over this result and write it to the active sheet. Once this is completed, select the two generated columns and go to 'Insert > Chart' to select a pie chart.

The fetched data plus the pie chart based on this data.
Click here to display the Apps Script file.
function fetch() {

  // Select the active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Specify the options that are sent as part of the request to the nodegoat API
  const options = {
   'method' : 'GET',
   'contentType' : 'application/json',
   'headers' : {
     'Authorization' : 'Bearer [Your Passkey]'
   }
  };

  // Receive the responses of the requests, parse it, and go to the position in the response that contains the data
  const reference_period = JSON.parse(UrlFetchApp.fetch('https://nodegoat.io/project/[Your Project ID]/data/type/[Your Object Type ID]/object', options))['data']['objects'];
  const statues = JSON.parse(UrlFetchApp.fetch('https://nodegoat.io/project/[Your Project ID]/data/type/[Your Object Type ID]/object', options))['data']['objects'];

  // Create an object to count the references per category
  let reference_period_amount = {};

  // Iterate of the categories
  for (const object_id in reference_period) {

    const object = reference_period[object_id];
    const object_name = object['object']['object_name'];
    reference_period_amount[object_id] = {'name' : object_name, 'amount' : 0};
  }

  // Iterate over the Statues and count the references to the categories
  for (const object_id in statues) {

    const object = statues[object_id];
    const arr_reference_period_values = (object['object_definitions']['239'] ? object['object_definitions']['239']['object_definition_ref_object_id'] : []);

    arr_reference_period_values.forEach(function(reference_period_object_id) {
      reference_period_amount[reference_period_object_id].amount++
    });
  }

  // Create an array that will contain an array for every category  
  let output = [];

  // Add the column heads to the output array
  output.push(['Category', 'Amount']);

  // Iterate over the categories and the amounts of references
  for (const object_id in reference_period_amount) {

    output.push([reference_period_amount[object_id].name,reference_period_amount[object_id].amount]);
  }

  // Remove any previously entered data in the active sheet and output the rows of the output array 
  sheet.getRange(1,1,5000,60).clearContent();
  sheet.getRange(1,1,output.length,2).setValues(output);
}

Use Apps Script to dynamically fetch all selected data of one nodegoat Project  

The two examples described above are useful when you want to request a specific set of data. If you want to get all data from one or multiple Object Types, it is more efficient to first query the 'model' endpoint of the nodegoat API and use the response to query the 'data' endpoint of the nodegoat API.

In order to be flexible in the endpoints and projects to be used, you first create a configuration sheet that will contain the information that is necessary to request both the 'model' as well as the 'data' endpoint of the nodegoat API. Specify a cell in which you enter the endpoint you want to query (e.g. 'https://nodegoat.io'), a cell for your authentication token, and a cell for the identifier of the project you want to query.

In the Apps Script editor you start your script by getting the values specified in your configuration sheet. These will be used for fetching the model as well as for fetching the data.

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const config_sheet = spreadsheet.getSheetByName('config');
const endpoint = config_sheet.getRange('B3').getValue();
const token = config_sheet.getRange('B4').getValue();
const project_id = config_sheet.getRange('B5').getValue();

const options = {
  'method' : 'GET',
  'contentType' : 'application/json',
  'headers' : {
   'Authorization' : 'Bearer '+token
  }
};

Next, you create two functions: fetchModel() and fetchData(). To be able to call these functions from your sheet, you can add buttons to your configuration sheet and assign the specified functions to these buttons. Go to 'Insert > Drawing' and create a button. After you have inserted the drawing you right click the drawing and click 'Assign a script'. Write the name of the function to connect the function to the drawing. After you have done this, the drawing works like a button and will run the script every time you click it.

The configuration sheet after clicking the 'Fetch Model' button.
Click here to display the Apps Script file.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const config_sheet = spreadsheet.getSheetByName('config');
const endpoint = config_sheet.getRange('B3').getValue();
const token = config_sheet.getRange('B4').getValue();
const project_id = config_sheet.getRange('B5').getValue();

const options = {
  'method' : 'GET',
  'contentType' : 'application/json',
  'headers' : {
   'Authorization' : 'Bearer '+token
  }
};

function fetchModel() {

  const types = JSON.parse(UrlFetchApp.fetch(endpoint+'/project/'+project_id+'/model/type', options))['data']['types'];
  let types_count = 0;
  let output = [];
  output.push(['Type ID','Name','Class']);

  for (const type_id in types) {

    const type_name = types[type_id]['type']['name'];
    const type_class = types[type_id]['type']['class'];    
    output.push([type_id,type_name,type_class]);
    types_count++;
  }

  config_sheet.getRange(7,1,5000,60).clearContent();
  config_sheet.getRange(7,1,5000,1).removeCheckboxes();
  config_sheet.getRange(7,2,types_count+1,3).setValues(output);
  config_sheet.getRange(8,1,types_count,1).insertCheckboxes();
}

function fetchData() {

  const types = config_sheet.getRange(8,1,100,4).getValues();
  types.forEach(function(type) {

    const checked = type[0];
    const type_id = type[1];
    const type_name = type[2];

    if (checked && type_id) {

      let data_sheet = spreadsheet.getSheetByName(type_name);
      if (data_sheet == null) {
        data_sheet = spreadsheet.insertSheet();
        data_sheet.setName(type_name);
      }

      let fetching = true;
      let limit = 1000;
      let offset = 0;
      let data = {};
      do {

        const fetched_data = JSON.parse(UrlFetchApp.fetch(endpoint+'/project/'+project_id+'/data/type/'+type_id+'/object?limit='+limit+'&offset='+offset+'', options))['data']['objects'];
        if (Object.keys(fetched_data).length > 0) {

          offset = offset + limit;
          data = Object.assign(data, fetched_data);

        } else {

          fetching = false;
        }
      }
      while (fetching); 

      const type = JSON.parse(UrlFetchApp.fetch(endpoint+'/project/'+project_id+'/model/type/'+type_id, options))['data']['types'][type_id];
      
      let arr_column_headings = ['Object ID','Object Name'];

      for (const object_description_id in type['object_descriptions']) {

         if (type['object_descriptions'][object_description_id]['object_description_value_type_base'] == 'reversal') {
           continue;
        }

        arr_column_headings.push(type['object_descriptions'][object_description_id]['object_description_name']);
      }

      for (const object_sub_details_id in type['object_sub_details']) {
        arr_column_headings.push(type['object_sub_details'][object_sub_details_id]['object_sub_details']['object_sub_details_name']);
      }

      let output = [];
      output.push(arr_column_headings);

      for (const object_id in data) {

        const object = data[object_id];
        let row = [object_id, object['object']['object_name']];

        for (const object_description_id in type['object_descriptions']) {

          const object_description = type['object_descriptions'][object_description_id];
         
          if (object_description['object_description_value_type_base'] == 'reversal') {
            continue;
          }

          if (object['object_definitions'][object_description_id]) {

            const value = (object_description['object_description_ref_type_id'] ? object['object_definitions'][object_description_id]['object_definition_ref_object_id'] : object['object_definitions'][object_description_id]['object_definition_value']);
            const separator = (object_description['object_description_value_type_settings']['separator'] ? object_description['object_description_value_type_settings']['separator'] : '|');

            row.push((Array.isArray(value) ? value.join(separator) : value));

          } else {

            row.push('');
          }
        }

        for (const object_sub_details_id in type['object_sub_details']) {
          
          let arr_sub = [];
          for (const object_sub_id in object['object_subs']) {

            const object_sub = object['object_subs'][object_sub_id]['object_sub'];

            if (object_sub['object_sub_details_id'] == object_sub_details_id) {

               arr_sub.push(object_sub['object_sub_date_start']+' / '+object_sub['object_sub_date_end']+' / '+object_sub['object_sub_location_geometry']);

               for (object_sub_description_id in object['object_subs'][object_sub_id]['object_sub_definitions']) {
                 
                 const object_sub_description_name = type['object_sub_details'][object_sub_details_id]['object_sub_descriptions'][object_sub_description_id]['object_sub_description_name'];
                 const object_sub_definition_value = object['object_subs'][object_sub_id]['object_sub_definitions'][object_sub_description_id]['object_sub_definition_value'];
                 arr_sub.push(object_sub_description_name+': '+object_sub_definition_value);
               }
            } 
          }

          if (arr_sub.length) {
            row.push(arr_sub.join('\n'))
          } else {
            row.push('');
          }
        }

        output.push(row);
      }

      data_sheet.getRange(1,1,5000,60).clearContent();
      data_sheet.getRange(1,1,output.length,arr_column_headings.length).setValues(output);
    }
  });
}

Clicking the 'Fetch Model' button will run the the 'fetchModel' function that parses the response of the 'model' endpoint of the nodegoat API and lists the returned Object Types together with a checkbox. Check any of the checkboxes and click the 'Fetch Data' button. The 'fetchData' function queries the 'data' endpoint of the nodegoat API based on the selected Object Types and generates a sheet per Object Type in which the data of all Objects of this Type will be written.