Data Source - Upload new File

The Data Source endpoint allows you to upload a new file as data source (type file) by POST request via the API. This can be useful, for example, if you want to regularly update your data source with an updated data file in an automated way. The file must be formatted either in JSON, XML, or CSV format and must be sent as form-data. (See. https://apidog.com/articles/what-is-form-data-content-type/)

The general format of the API endpoints is:

{YOURBASEURL}/api/custom/DataSource/ChangeFile/{DATASOURCEID}

Variable Parameters

Base URL

Depending on your data residency settings, you may need to look up {YOURBASEURL} to create the endpoint URL. You’ll find the BaseUrl under Settings > Custom External REST API tab above the User ID.

Data Source ID

To update an existing data source via API, you need the {DATASOURCEID}of the target data source that is to be replaced by the new file. You will find this ID at the very top of the Data Source Configuration screen (Data Source tab).

Limitations:

• a maximum of 1 request per minute

• a maximum of 100 requests per 24 hours

• will not invalidate already cached versions. This can be worked around by decreasing the cache minutes value.

Example Curl Request

curl -X POST \
  'https://external-data-for-jira.codefortynine.com/api/custom/DataSource/ChangeFile/{DATASOURCEID}' \
  -u 'username:password' \
  --form 'file=@/path/to/file/datasource_data.json'

Example Postman

URL

Method POST with generated URL:

Authorization

Basic Auth with username & password:

Body

The type of the parameter has to be set to File.

Example Google Sheet Apps Script

If a data source should be regularly updated from a Google Sheet, you can add an App Script to the sheet by going to Extensions and clicking Apps Script.

You can find more on these App Scripts in Google’s documentation.

When the Apps Script Project is created, you can use the following code in your Code.gs file:

function sendSheetToAPI() {
  const ui = SpreadsheetApp.getUi()
  const scriptProperties = PropertiesService.getScriptProperties();
  const baseUrl = scriptProperties.getProperty('edUrl');
  const sourceId = scriptProperties.getProperty('edSource');
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  // Convert data to JSON format
  const data = values.slice(1).map(row => {
    const o = {};
    row.forEach((c,i) => o[headers[i]] = c);
    return o;
  });
  // Create the JSON part as a Blob
  const jsonBlob = Utilities.newBlob(
    JSON.stringify(data, null, 2),
    'application/json',
    'data.json'
  );
  // API credentials for Basic Authentication
  const username = scriptProperties.getProperty('edUser');
  const password = scriptProperties.getProperty('edSecret');
  // Encode credentials to Base64
  const authString = 'Basic ' + Utilities.base64Encode(`${username}:${password}`);
  // Define your API endpoint
  const url = baseUrl + '/api/custom/DataSource/ChangeFile/' + sourceId;
  // Configure the request
  const options = {
    method: 'post',
    payload: {
      file: jsonBlob
    },
    headers: {
      Authorization: authString
    },
    muteHttpExceptions: true,
  };

  Logger.log('Sending request to API at %s', url);
  //Send request
  try {
    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();
    Logger.log('API Response Code: %s', responseCode);
    Logger.log('API Response Body: %s', responseBody);
    if (responseCode == 200.0) {
      ui.alert("The file has been sucessfully updated")
    }
      
  } catch (error) {
    Logger.log('Error occurred: %s', error.message);
  }
}
// Add button to trigger sendSheetToAPI. 'On Open' trigger for this function needs to be added to project.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Custom Actions')
    .addItem('Send Sheet to Data Source', 'sendSheetToAPI')
    .addToUi();
}

The main script retrieves important variables from script properties, which you can set in your Apps Script project under ⚙️ Project Settings > Script Properties. The following properties need to be created and set to the values of the variables described above and your authentication variables:

Then you need to create a trigger in the project to trigger the onOpen function in the Code.gs file when a User opens the sheet. This function will add the Custom Actions menu and its Send Sheet to Data Source option, which will trigger the main sendSheetToAPI function.

Now you should be able to open the sheet and see the Custom Actions menu and its Send Sheet to Data Source option. You should also get a confirmation message if everything went as planned. Otherwise, you can check your logs in your Apps Script project under Executions.