7 posts tagged with "Sheetloom"

This guide will walk you through the complete workflow of using the Sheetloom API, from authentication to downloading your generated spreadsheets.

Overview​

The Sheetloom API allows you to programmatically generate spreadsheets from templates. The workflow consists of four main steps:

1. Authenticate with AWS Cognito to obtain access tokens
2. Check available templates for your tenant
3. Run Sheetloom to generate a spreadsheet from a template
4. Download the generated spreadsheet

Prerequisites​

• A valid Sheetloom account with tenant credentials
• curl installed on your system
• jq installed (optional, but helpful for parsing JSON responses)

Step 1: Authentication​

The first step is to authenticate with AWS Cognito to obtain your access and ID tokens. These tokens will be used for all subsequent API calls.

Authentication Request​

curl -X POST "https://cognito-idp.{region}.amazonaws.com/" \
  -H "Content-Type: application/x-amz-json-1.1" \
  -H "X-Amz-Target: AWSCognitoIdentityProviderService.InitiateAuth" \
  -d '{
    "AuthParameters": {
      "USERNAME": "your-email@example.com",
      "PASSWORD": "your-password"
    },
    "AuthFlow": "USER_PASSWORD_AUTH",
    "ClientId": "your-cognito-client-id"
  }'

Understanding the Response​

The response will contain several tokens:

• AccessToken: Used for API authorization
• IdToken: Used for API authorization (some endpoints may require this)
• RefreshToken: Used to obtain new tokens when they expire

Save these tokens securely. You'll need the IdToken for the next steps.

Example response structure:

{
  "AuthenticationResult": {
    "AccessToken": "eyJraWQiOiJ...",
    "IdToken": "eyJraWQiOiJ...",
    "RefreshToken": "eyJjdHkiOiJ...",
    "ExpiresIn": 3600
  }
}

Step 2: Check Available Templates​

Before generating a spreadsheet, you may want to see what templates are available for your tenant.

List Templates Request​

curl -X GET "https://{api-gateway-endpoint}/core/templates/" \
  -H "Authorization: {id_token_here}" \
  -H "x-tenant-id: {your-tenant-id}"

Replace:

• {api-gateway-endpoint} with your API Gateway endpoint
• {id_token_here} with the IdToken from Step 1
• {your-tenant-id} with your tenant identifier

This will return a list of available templates that you can use to generate spreadsheets.

Step 3: Run Sheetloom​

Now that you have your authentication token and know which templates are available, you can generate a spreadsheet.

Generate Spreadsheet Request​

curl -X POST "https://{api-gateway-endpoint}/core/main/?domain={domain}&template={template-path}&isUser={true|false}&fileName={filename}" \
  -H "Authorization: {id_token_here}" \
  -H "x-tenant-id: {your-tenant-id}" \
  -H "Content-Type: application/json"

Parameters:

• domain: The domain context for the generation (e.g., localhost:1234)
• template: The full path to the template file (e.g., {tenant-id}-sheetloom-templates/default/templates/regulatory.xlsx)
• isUser: Boolean indicating if this is a user-initiated request
• fileName: The base name for the generated file

Example:

curl -X POST "https://{api-gateway-endpoint}/core/main/?domain=localhost:1234&template={tenant-id}-sheetloom-templates/default/templates/regulatory.xlsx&isUser=true&fileName=regulatory" \
  -H "Authorization: {id_token_here}" \
  -H "x-tenant-id: {your-tenant-id}" \
  -H "Content-Type: application/json"

This will trigger the Sheetloom generation process. The response will typically include information about the generation job, including the file path where the generated spreadsheet will be stored.

If the template has any parameters, these can be added into the request using a -d flag on the curl request.

Example

curl -X POST "https://{api-gateway-endpoint}/core/main/?domain=localhost:1234&template={tenant-id}-sheetloom-templates/default/templates/regulatory.xlsx&isUser=true&fileName=regulatory" \
  -H "Authorization: {id_token_here}" \
  -H "x-tenant-id: {your-tenant-id}" \
  -H "Content-Type: application/json" \
    -d '{ \
    "parameterName1": "value1", \
    "parameterName2": "value2", \
    "parameterName3": "value3" \
  }'

Step 4: Download the Generated Sheet​

Once the spreadsheet has been generated, you can download it using the download endpoint.

Download Request​

The download endpoint requires the file path and returns a presigned URL for secure download:

curl -s -X GET "https://{api-gateway-endpoint}/core/download/" \
  -G \
  --data-urlencode "filePath={file-path}" \
  --data-urlencode "presigned=true" \
  -H "Authorization: {your_token_here}" \
  -H "x-tenant-id: {your-tenant-id}" | \
  jq -r '.downloadUrl' | \
  xargs curl -f -o "{output-filename}.xlsx"

Breaking down the command:

1. The first curl request gets the presigned download URL
2. jq -r '.downloadUrl' extracts the download URL from the JSON response
3. The second curl downloads the file using the presigned URL and saves it locally

Example:

curl -s -X GET "https://{api-gateway-endpoint}/core/download/" \
  -G \
  --data-urlencode "filePath=users/{email}/spreadsheets/templates/regulatory/regulatory.xlsx" \
  --data-urlencode "presigned=true" \
  -H "Authorization: {your_token_here}" \
  -H "x-tenant-id: {your-tenant-id}" | \
  jq -r '.downloadUrl' | \
  xargs curl -f -o "regulatory.xlsx"

This will download the generated spreadsheet to your local machine as regulatory.xlsx.

Complete Workflow Example​

Here's a complete example script that ties everything together:

#!/bin/bash

# Configuration
REGION="eu-west-1"
COGNITO_CLIENT_ID="your-cognito-client-id"
API_ENDPOINT="https://your-api-gateway.execute-api.region.amazonaws.com/dev"
TENANT_ID="your-tenant-id"
USERNAME="your-email@example.com"
PASSWORD="your-password"
TEMPLATE_PATH="your-tenant-sheetloom-templates/default/templates/regulatory.xlsx"
OUTPUT_FILE="regulatory.xlsx"

# Step 1: Authenticate
echo "Authenticating..."
AUTH_RESPONSE=$(curl -s -X POST "https://cognito-idp.${REGION}.amazonaws.com/" \
  -H "Content-Type: application/x-amz-json-1.1" \
  -H "X-Amz-Target: AWSCognitoIdentityProviderService.InitiateAuth" \
  -d "{
    \"AuthParameters\": {
      \"USERNAME\": \"${USERNAME}\",
      \"PASSWORD\": \"${PASSWORD}\"
    },
    \"AuthFlow\": \"USER_PASSWORD_AUTH\",
    \"ClientId\": \"${COGNITO_CLIENT_ID}\"
  }")

ACCESS_TOKEN=$(echo $AUTH_RESPONSE | jq -r '.AuthenticationResult.AccessToken')
ID_TOKEN=$(echo $AUTH_RESPONSE | jq -r '.AuthenticationResult.IdToken')

echo "Authentication successful!"

# Step 2: Check templates (optional)
echo "Checking available templates..."
curl -X GET "${API_ENDPOINT}/core/templates/" \
  -H "Authorization: ${ACCESS_TOKEN}" \
  -H "x-tenant-id: ${TENANT_ID}"

# Step 3: Generate spreadsheet
echo "Generating spreadsheet..."
curl -X POST "${API_ENDPOINT}/core/main/?domain=localhost:1234&template=${TEMPLATE_PATH}&isUser=true&fileName=regulatory" \
  -H "Authorization: ${ID_TOKEN}" \
  -H "x-tenant-id: ${TENANT_ID}" \
  -H "Content-Type: application/json"

# Step 4: Download the generated sheet
echo "Downloading spreadsheet..."
FILE_PATH="users/${USERNAME}/spreadsheets/templates/regulatory/regulatory.xlsx"
curl -s -X GET "${API_ENDPOINT}/core/download/" \
  -G \
  --data-urlencode "filePath=${FILE_PATH}" \
  --data-urlencode "presigned=true" \
  -H "Authorization: ${ID_TOKEN}" \
  -H "x-tenant-id: ${TENANT_ID}" | \
  jq -r '.downloadUrl' | \
  xargs curl -f -o "${OUTPUT_FILE}"

echo "Download complete! File saved as ${OUTPUT_FILE}"

Error Handling​

When working with the API, you may encounter various errors:

• 401 Unauthorized: Your token has expired or is invalid. Re-authenticate to get a new token.
• 403 Forbidden: You don't have permission to access the requested resource. Check your tenant ID and user permissions.
• 404 Not Found: The template or file path doesn't exist. Verify the template path and file path are correct.
• 500 Internal Server Error: An error occurred on the server. Check the response body for details.

Security Best Practices​

1. Never commit tokens to version control: Always use environment variables or secure credential storage
2. Rotate tokens regularly: Use refresh tokens to obtain new access tokens before they expire
3. Use HTTPS only: All API calls should use HTTPS endpoints
4. Validate responses: Always check response status codes and handle errors appropriately
5. Store credentials securely: Use secure credential management tools rather than hardcoding credentials

Conclusion​

The Sheetloom API provides a powerful way to programmatically generate spreadsheets from templates. By following this guide, you can integrate Sheetloom into your automation workflows, CI/CD pipelines, or custom applications.

For more information or support, please refer to the Sheetloom documentation.

Filling business decision models with essential data captured in NetSuite™ can be: tiring, confusing, repetitive and error prone. The good news for the brave souls, doing all that manual labour, is Sheetloom. Sheetloom is the game-changing SaaS solution that seamlessly automates the injection of NetSuite™ data straight into dynamic Excel decision models, pivots, dashboards and reports.

Credit Control is a challenging and time-consuming affair which, if managed incorrectly, can lead to cash flow problems and even business failure. Keeping track of customer payment history, to indicate potential problems, is an important task but often; labour intensive, complex and error prone.

For freight management operations, reporting on how long shipments are taking between milestones is an important metric both for clients and operational teams. However, gathering, collating, and presenting the data can often be a tedious, time consuming, inconsistent and error prone process.

Accountants regularly face the drudgery and risk of manually consolidating business transactions from different companies spread out over various reporting jurisdictions.

A finance company used a mix of the latest digital tech and a human touch to bring a different approach to small businesses lending.

A crucial part of the loan review process required the company to credit score each applicant. Testing of the credit score process proved very time consuming using traditional methods. The finance company would manually; select applicants, collect individual responses and then consolidate results from a credit checking service.

All Business Intelligence (BI) vendors have a dirty little secret. It’s hidden under a weighty digital rock, keeping all those electronic worms company. It’s shared by Looker, Pentaho, Power BI, Tableau, SAP, Quicksight, et al.

It’s obvious, but seldom noticed. Take a deep breath, here it is;

All BI clients use Excel, way more than the BI vendors care to admit.