Skip to main content

How to pre-process rates for import?

Sometimes, rate tables received from providers may have formats that require adjustments before they can be imported into JeraSoft Billing. This guide shows how to pre-process these tables either by using our dedicated pre-processor — available as a Google Apps Script library for Google Workspace — or by providing guidance to help you create your own customized solution.

Our pre-processor is extended for the formats of various widely used providers. If your format is not supported, please contact our support team to discuss the possibility of extending it.

To use our dedicated pre-processor, you need to provide the email addresses of the Google accounts that will be involved in these operations to our support team. This is required to grant access to the solution within Google infrastructure.

info

Please note that our dedicated pre-processor is available only to customers with an active support subscription.

Manual pre-processing​

Step-by-step guide​

  1. Upload the rate sheet you want to convert to Google Drive.
  2. Make sure the file is converted to Google Sheets format.
    1. If it is not, double-click the document to open it.
    2. In the menu, click File > Save as Google Sheets.
  3. In the menu of the document, click Extensions > Apps Script.
  4. Add the JeraSoft Rates library:
    1. In the left panel under Libraries, click +.
    2. Set Script ID to 1WC3tT7jFZb4RsYh87LHUyyVUX57sOjctgLUZVlxXMiRQm5IvPxhFxc4W
    3. Click Look Up.
    4. Keep Version to the latest available.
    5. Keep the identifier to JeraSoftRates.
    6. Click Add.
  5. In the main text editor, replace all existing code with the following: 
    function onOpen() {
      JeraSoftRates.onOpen();
    }
  6. Click the Save icon or press Ctrl+S.
  7. In the top toolbar, click Run.
  8. When asked for permissions, grant them:
    1. Click Review permissions.
    2. Select your account.
    3. On the "Google hasn’t verified this app" page, click Advanced and then Go to Untitled project (unsafe).
    4. Finally, click Allow to grant access for the script to the document.
  9. At this point, you can return to the document and see JeraSoft as the rightmost option in the menu.
  10. Select the vendor and format you need to pre-process.
  11. Once the script finishes, you can download the file in XLSX format and use the regular rates import in JeraSoft Billing.

Automatic pre-processing​

To automate the process of pre-processing and importing rates, we will use the Make.com service. This setup will create a workflow triggered by an incoming email with a specific subject in a designated mailbox, where rate notifications are sent from your vendors. The workflow will then:

  1. Take the email attachment and upload it to Google Drive.
  2. Execute the pre-processing via an HTTP API request.
  3. Download the pre-processed file.
  4. Send it to another dedicated email that’s configured as a data source for Automatic Rates Import in JeraSoft Billing.

This approach automates the full process, from receiving the rate notification to uploading rates into JeraSoft Billing.

note

While you may use other workflow automation services besides Make.com, ensure that the service supports HTTP requests with long timeouts (>= 120 seconds). Services like Zapier have a hard limit of 30 seconds, which is insufficient for the pre-processing request to complete.

Google Apps Script setup​

This setup process only needs to be performed once for all future automations.

  1. Go to your Google Drive. We recommend using Shared Drives for this setup, but you may use My Drive if preferred.
  2. Create a Shared Drive named JeraSoft Rates Processing.
  3. While within this drive, click New > More > Google Apps Script.
  4. Add the JeraSoft Rates library:
    1. In the left panel under Libraries, click +.
    2. Set the Script ID to 1WC3tT7jFZb4RsYh87LHUyyVUX57sOjctgLUZVlxXMiRQm5IvPxhFxc4W.
    3. Click Look Up.
    4. Set Version to the latest available.
    5. Keep the identifier as JeraSoftRates.
    6. Click Add.
  5. In the main text editor, replace all existing code with the following: 
    function doGet(e) {
      return JeraSoftRates.doGet(e);
    }
  6. Rename the script to JeraSoft Rates Pre-processing by clicking on Untitled project at the top.
  7. Click the Save icon or press Ctrl+S.
  8. Deploy the script to make it accessible via HTTP requests:
    1. Click Deploy > New Deployment in the top right corner.
    2. Click the gear icon next to Select type and choose Web app.
    3. Enter HTTP API in the description.
    4. Set Execute as to Me.
    5. Change Who has access to Anyone.
    6. Click Deploy.
    7. On the next screen, click Authorize access where it says, "The Web App requires you to authorize access to your data."
    8. Select your account.
    9. On the "Google hasn’t verified this app" page, click Advanced and then Go to Untitled project (unsafe).
    10. Finally, click Allow to grant access for the script to the document.
  9. On the final screen, copy and save the Web app URL. You will need this in the workflow setup.

Workflow setup​

This step must be performed for each type of vendor rate sheet you want to automate for pre-processing and import into JeraSoft Billing.

This guide assumes you have a Make.com account and are using Gmail for handling emails. If you use a different email service, replace "Gmail" steps with "Email" or the relevant service supported by Make.com.

  1. In the Make.com panel, click + Create a new scenario.

  2. Set up the workflow trigger:

    1. Select Gmail in the service list, or choose Email if using another email provider.
    2. Select Watch Emails.
    3. Click Create a connection to connect the trigger to your mailbox.
    4. Name the connection (e.g., "Rates Manager").
    5. Click Sign in with Google and select your email (where vendor notifications are sent), then click Continue and Allow.
    6. Under Folder, select INBOX.
    7. Keep Filter set to Simple filter.
    8. In Criteria, select All emails.
    9. Optionally, set Mark email message(s) as read when fetched to Yes.
    10. Keep Maximum number of results to 1.
    11. Click Show advanced settings and enter the vendor’s Sender email address and/or Subject to filter for specific vendor emails.
    12. Click OK and set Choose where to start to From now on.

  3. Add a step to upload the email attachment to Google Drive:

    1. Click + Add another module to the right of the trigger.
    2. Select Google Drive and choose Upload file.
    3. Create a new connection to Google Drive if using a separate account for rates storage, or select an existing one.
    4. In Enter a Folder ID, choose Select from the list.
    5. Create separate folders within the Shared Drive you set up earlier for each vendor.
    6. In New Drive Location, select Shared Drive and choose the drive created earlier.
    7. In New Folder Location, select the folder for the current vendor.
    8. For File Name, select File name under Attachments[].
    9. For Data, select Data under Attachments[].
    10. Click Show advanced settings and set Convert a file to Yes and Target Google File Type to application/vnd.google-apps.spreadsheet.
    11. Click OK.

  4. Add a step to pre-process the uploaded file:

    1. Click + Add another module.
    2. Select HTTP and choose Make a request.
    3. Enter the Web app URL from the pre-processor script setup in the URL field.
    4. Keep Method as GET.
    5. Under Query String, add:
      • Name: pluginId
      • Value: The ID for the plugin to pre-process the file (e.g., ibasisOBR)
    6. Add another parameter:
      • Name: spreadsheetId
      • Value: Select File ID from the panel on the right.
    7. Set Parse response to Yes.
    8. Click OK.

  5. Add a step to download the pre-processed file:

    1. Click + Add another module.
    2. Select Google Drive and choose Download a File.
    3. Select the same connection used in the "Upload file" step.
    4. Set Enter a File ID to Enter manually.
    5. In File ID, select File ID from the Google Drive section in the panel on the right.
    6. Click OK.

  6. Add a step to send the pre-processed file to the email monitored by JeraSoft Billing:

    1. Click + Add another module.
    2. Select Gmail and choose Send an email.
    3. Use the same connection as in the trigger.
    4. In To, enter the email monitored by JeraSoft Billing for rate imports.
    5. Set Subject by selecting Subject from the Gmail section in the panel, or enter a new subject if reusing the same email.
    6. In Content, select Text content from Gmail.
    7. Click Add an attachment and keep Google Drive - Download a File selected.
    8. Click OK.

  7. Click Save in the scenario toolbar to save your workflow.

Testing​

To ensure your workflow functions as expected, adjust the trigger settings to fetch emails from your inbox, send yourself a test email with a vendor attachment, and click Run Once in the scenario toolbar. If correctly set up, the file will be uploaded to Google Drive, processed, and sent to the final email.

This workflow can operate independently or with the Automatic Rates Import for full automation.

Supported Plugins​

The following plugin IDs are currently supported for pre-processing:

Plugin IDDescription
dtOBRDeutsche Telecom OBR
ibasisOBRiBasis OBR
orangeOBROrange OBR

Custom pre-processor​

This guide covers how to pre-process rates in widely-used formats. However, if you have specific formats that require custom pre-processing, you can follow a similar approach using Google Apps Script.

The general idea is for your custom script to create new sheets within the same file, formatting the data into a structure that is easier to import. We recommend naming the columns to match the fields in JeraSoft Billing, which allows for automatic column detection during the import process.

If you'd like more assistance with creating a custom script or adapting this approach for your needs, please contact our support team.