What it does
This macro allows you to connect to API endpoints (application protocol interfaces) to import data directly from Excel to applications and export data from applications directly to Excel. This can save a significant amount of time compared to all the steps it can take to manually import or export data using other methods (if other methods are even available). This can be used with any application that provides open APIs or APIs that you have access to. Up to 20 separate accounts across one or more different types of applications can be set up, and you can easily toggle between them. This is extremely helpful if you work with several different clients and need to provide/retrieve data on a regular basis or if you have several applications you want to import/export data with. You get to decide which API endpoints to use and how they are organized in the ribbon within a separate ribbon tab called XLEV8 API that is displayed once you configure it for the first time running the Configure API Settings macro.

When to use it
When you routinely provide or retrieve data to applications where that data is already in Excel or you need it to be in Excel, this is a great option for streamlining the process. Often applications lack direct-to-Excel export options but provide APIs to get that data. Importing data to applications can also be difficult, where version control and unclear validations can make it a frustrating process of trial-and-error.

Why to use it
This method of importing/exporting data can eliminate many manual steps by cutting out unnecessary steps that often add the potential for errors. For importing, this can eliminate the need to save a file, navigate to import screens, navigate to the file to import, navigate to errors when they occur, and provide additional validation that proactively reduces errors.

Default shortcut
None

Other Details

• Category: Productivity / Other
• Difficulty: 5/5
• Usage/frequency: 3/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 10/23/2022
• Tags: API, import, export, data

Related Macros
Toggle API Account
Test API Credentials

Other Articles
None

None

Prerequisites
Find the API documentation for any applications you want to connect to.  This is often in the support or developer section of your application.

Instructions
After you have identified the source(s) you want to connect to, run the Configure API Settings macro.  The first time you run it, it will create an Excel file named XLEV8_API.xlsx.  This Excel file contains three sheets where you’ll configure your API accounts/credentials, the API import/export calls you want to leverage, and the settings for those API calls.  Follow the instructions below to configure settings for the APIs you’d like to leverage, then run the Configure API Settings macro again to save your settings.  Note: it is important to run the Configure API Settings macro again to save and process your settings.  Below are instructions for each of those three sheets.

API_Credentials fields:

• Column A – Credential Fields – some of these fields are required, and some are optional.  The requirement depends on how the specific API works.  Use the cell comments within the fields in column A for instructions and examples.
• Columns B:U – Credential Values – enter the applicable credential values for each application/account in these columns, starting from the left (column B).  Ensure give each account a unique name so you can easily toggle between them, but name each application consistently because API call settings will be linked to the application name as listed in row 2.  For any unneeded columns, just leave the fields blank.  Note that at least one account must be configured to save the settings.
• Use the Test API Credentials macro to test out the credentials to see if you can connect to the API and retrieve a token.  The token is used to when making API calls to send or retrieve data.  When you get a success message, generally that means you have entered valid credentials and can proceed to the next sheet.
• Once you have configured your API credentials, use the Toggle API Account macro to switch between any of the up to 20 accounts you can configure.  It will prompt you with the accounts you have configured so you can switch between them. 

API_Calls fields:

• Group Label – this is the label to give to the ribbon section/group you want the API call to be in.  Up to 8 unique/separate groups can be used to hold button menus.
• Menu Label – this is the label to give to the menu of API calls.  Each group can have up to four menus, and each menu can contain up to 10 unique buttons.
• Button Label – this is the label to give to each button (housed within menus) in the XLEV8 API ribbon.  The button label is also searchable in the Search Macros box with an “[API]” prefix.
• Description – this is a description of the API call that will be displayed when hovering over the button in the ribbon, and when searching in the Macro Search box.
• Application – this links the API call to one of the applications listed on the API_Credentials sheet.  There must be an exact match to one of the applications in the API_Credentials sheet, otherwise an error will result.
• Endpoint URL – this is the URL that should be used to connect to the API call.  Follow the instructions in the header cell for including variables within the URL, such as the domain name, an import ID, name, etc.
• Data Connection Type – this tells how to connect to the API call.  Usually values are GET, PUT, or POST.  Use the drop-down list to select the appropriate value.
• Data Direction – this indicates whether the API call will import (upload data from Excel) or export (download data to Excel).
• Content Type – this tells the API call what type/format of data will be transported.  Generally this is “application/json” but this can be found in the API call documentation.
• Data Format – this helps describe the format of the data structure that should be imported/exported and whether or not to include/exclude headers.  It is assumed that data to import is tabular and starts with headers in row 1 of the import sheet.
• Sheet Name – this is the sheet that should contain the data to import from Excel, or the sheet name to give data that is exported to Excel.  This will also be linked to the API_Fields sheet to determine which fields will be imported/exported.
• Status URL – this is the URL used to check the status of a data import.  Similar to the Endpoint URL, this can contain variables – see the header cell comment for detailed instructions.
• Error URL – this is the URL used to retrieve errors when a data import is not successful.  Similar to the Endpoint URL, this can contain variables – see the header cell comment for detailed instructions.
• Success Code – this is the expected code for a successful data import.  When this code is not returned, any errors or other details are displayed so you can troubleshoot.
• Argument Variable – this is used when providing variable argument/sdata points along with the URL call (i.e. import identifier).  Follow the instructions in the header cell comment to use a prompt or cell value of a specific sheet to easily provide variable arguments.
• Response Variable – this is the name of the variable that should be used to check the the response (generally an import ID).
• Dependency – generally this is used alongside an argument variable – this helps supply conditional arguments for the endpoint URL.  Follow the instructions in the header cell comment to use a dependency.
• Response Message – this is displayed with a successful API call (generally exports) to provide the user with guidance on the next step to perform.
• Menu Icon – this is used to configure what icon should be displayed along with the menu label.  This file contains a list of valid icon names you can supply. 
• Button Icon – this is used to configure what icon should be displayed along with the button label.  This file contains a list of valid icon names you can supply. 

API_Fields fields:

• Sheet Name – this is the import/export sheet where the field should be listed.
• Field Name/Comment – this is the header/field name that will be displayed in the import/export sheet.  For imports, include helpful instructions as cell comments – these will be copied to the sheet created for importing data.  For exports, often the data is exported in a structured JSON format.  To convert that structure to a user-friendly tabular format, follow the instructions in the header cell comment for pointing to the specific data to export.  If export fields are not listed and the data is in a JSON structure, the entire JSON tree will be exported – this can help determine the syntax for exporting specific data.
• Required (Yes/No) – for importing data, this is used to determine if the field is required or not when validating data before sending.
• Validation – for importing data, include any validation in the data validation within the cell for the field in that row.  Examples: dates, numbers, text length, ranges, valid values drop-down list, etc.