Import REST API in Google Sheet AppScript - JSON / SOAP XML / CSV

UPDATE: ZappySys has released a brand new API Connector for Google Sheets Online which makes it much simpler to Read/Write Google Sheets Data in ODBC compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

In our previous few articles we saw various use cases of Data Gateway. Now let’s look at one more interesting use case of Data Gateway. We will look at how to Import REST API in Google Sheet (JSON / XML API or File) by calling Google AppScript (i.e. Macro Code like in Excel but Google use JavaScript Language).

With some simple JavaScript AppScript code in Google Sheet you can load virtually any REST API / SOAP API or read data from JSON / XML / CSV File saved on your local environment. You can also populate data in Google Sheet using SSIS but that requires you to lean SSIS. Approach listed in this article requires minimum investment and very little learning. If you are SSIS developer and you want to use ETL approach then please check the article.

Requirements

Make sure you have a valid Google account and access to Google Sheet Service
Download and Install ZappySys ODBC PowerPack

How does it work?

So you must be wondering how google AppScript can load data from REST API (or Local files ) ? Here are high level steps.

first of all, install ZappySys ODBC PowerPack and make sure ZappySys Data Gateway is running (By default it should be).
Configure the Firewall to allow Port 5000 (or some other if you change default port) so Google SpreadSheet can connect to machine where ZappySys Data Gateway is running
Google Server (i.e. AppScript) sends SQL query (for REST API Service / Files) using MSSQL JDBC Driver to ZappySys Data Gateway (TDS Protocol used by JDBC Driver and ZappySys Data Gateway).
ZappySys Data Gateway Parses SQL Query request and translates to REST API call or File Read request
JSON / XML / CSV data is parsed into Rows and Columns and Sent back to Google Sheet Service
Your REST API / File data is rendered in rows / columns in Google Sheet

Step-By-Step : Import REST API in Google Sheet

Now let’s get started. In next few sections we will call sample JSON REST API (Example OData Service) and load it into Google Sheet.

Configure ZappySys Data Gateway

Now let's look at how to configure ZappySys Data Gateway. This feature acts as a bridge between Client App and ZappySys Drivers. Using data gateway you can use ZappySys Drivers inside applications / operating systems where ZappySys drivers may not be available directly for some reason (e.g. You don't have access to Server for Installation or System does not support ODBC drivers like JAVA programs). Click here to read more on various use cases of Data Gateway.

Configure Data Gateway User / Port

Now let's look at steps to configure Data Gateway after installation. We will also create a sample data source for ODATA API (i.e. JSON based REST API Service).

Assuming you have installed ZappySys ODBC PowerPack using default options (Which also enables Data Gateway Service)
Search "Gateway" in your start menu and click ZappySys Data Gateway

Open ZappySys Data Gateway
First make sure Gateway Service is running (Verify Start icon is disabled)
Also verify Port on General Tab

Port Number setting on ZappySys Data Gateway
Now go to Users tab. Click Add icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.

Add Data Gateway User

Configure Data Source

After user is added, go to Data Sources tab. Click Add icon to create new data source. Select appropriate driver based on your API / File format. You can choose Generic ODBC option to read data from ODBC DSN or use Native Driver option.
```
NOTE: Whenever possible use native driver option for better performance / security and ease of use.
```
Add Gateway Data Source (Native JSON Driver)
Click on "Edit" under Data source and configure as per your need (e.g. Url, Connection, Request Method, Content Type, Body, Pagination etc.). For this demo we are going to pick simple JSON REST API which doesn't need any authentication. Enter following URL.
```
https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json
```
You can also view response structure and select default hierarchy (i.e. Filter) like below (Select Array Icon) for data extraction.

Configure JSON API Data source

Test SQL Query / Preview Data

Now go to Preview Tab. You can click Preview button to execute default query OR Select Table name from dropdown to generate SQL with column names.

JSON / REST API Driver Query Preview / Query Examples (Read REST API or JSON Files)
You can also click Query Builder to generate SQL using different options in WITH clause. ANy setting you specify in WITH clause will override UI settings we applied in previous steps.

Using SQL Query Builder (For Files or REST / SOAP API - JSON / XML / CSV Format)
There is another useful option for code generation. Select your Language and quickly copy code snippet. See below Example of XML Driver Query to call SOAP API.

Generate Example Code for ZappySys Driver
Click OK to Close Data Source UI
Once data source is tested and configured you can click Save button in the Gateway UI toolbar and click Yes for Restart Service.

Open Firewall Port

Next step is to make sure your server where you installed ODBC PowerPack is exposed to internet and you allow Port 5000 for inbound traffic. If you changed default port for Gateway then use that accordingly in firewall configuration.

Call AppScript to load REST API data into Google Sheet

Once you done with Data Gateway configuration you can move forward to creating Google Sheet.

Create a new Google Sheet. Let’s call it Gateway Data
From the tools menu click Script Editor

Write Following Code and Click Save

function ApiToGoogleSpreadSheet(sqlQuery) {
  if(sqlQuery==null){
    //Default Query
    sqlQuery="select * from $ WITH( \
Filter='$.value[*]', \
SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json' \
) ";
  }
    var address = 'MyGatewayServerIpOrFullDomainName:5000'; // ZappySys Data Gateway Server Name / IP
    var user = 'tdsuser'; // ZappySys Data Gateway User Name
    var userPwd = 'MyTdsP@ssword123';  //Password of ZappySys Data Gateway User
    var db = 'odata_native';
    var msSqlUrlSyntax ='jdbc:sqlserver://'
    var dbUrl = msSqlUrlSyntax + address + ';databaseName=' + db;
    var conn = Jdbc.getConnection(dbUrl, user, userPwd);
    var start = new Date();
    var stmt = conn.createStatement();
    //stmt.setMaxRows(10);
    var results = stmt.executeQuery(sqlQuery);
    var rsMeta = results.getMetaData();
    var numCols = rsMeta.getColumnCount();
    //var numRows = results.getFetchSize()
    //Logger.log(numRows)
    var dataArray = [];
    //get column names
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsMeta.getColumnName(col+1);
    }
  
    //resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
    //rowString.push(results.getString(col + 1))
   
    dataArray[dataArray.length]=headers[0];
    
    while (results.next()) {
        var rowString = [];   
        rowString = [];

        for (var col = 0; col < numCols; col++)     
        {
            rowString.push(results.getString(col + 1));
        }

        dataArray[dataArray.length]=rowString;
    }

    results.close();
    stmt.close();

    var end = new Date();
    Logger.log('Time elapsed: %sms', end - start);

     //Logger.log("dataArray =" + dataArray)
    return dataArray
 
}

function ApiToGoogleSpreadSheet(sqlQuery) {

if(sqlQuery==null){

//Default Query

sqlQuery="select * from $ WITH( \

Filter='$.value[*]', \

SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json' \

) ";

}

var address = 'MyGatewayServerIpOrFullDomainName:5000'; // ZappySys Data Gateway Server Name / IP

var user = 'tdsuser'; // ZappySys Data Gateway User Name

var userPwd = 'MyTdsP@ssword123'; //Password of ZappySys Data Gateway User

var db = 'odata_native';

var msSqlUrlSyntax ='jdbc:sqlserver://'

var dbUrl = msSqlUrlSyntax + address + ';databaseName=' + db;

var conn = Jdbc.getConnection(dbUrl, user, userPwd);

var start = new Date();

var stmt = conn.createStatement();

//stmt.setMaxRows(10);

var results = stmt.executeQuery(sqlQuery);

var rsMeta = results.getMetaData();

var numCols = rsMeta.getColumnCount();

//var numRows = results.getFetchSize()

//Logger.log(numRows)

var dataArray = [];

//get column names

var headers = new Array(new Array(numCols));

for (var col = 0; col < numCols; col++){

headers[0][col] = rsMeta.getColumnName(col+1);

}

//resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);

//rowString.push(results.getString(col + 1))

dataArray[dataArray.length]=headers[0];

while (results.next()) {

var rowString = [];

rowString = [];

for (var col = 0; col < numCols; col++)

{

rowString.push(results.getString(col + 1));

}

dataArray[dataArray.length]=rowString;

}

results.close();

stmt.close();

var end = new Date();

Logger.log('Time elapsed: %sms', end - start);

//Logger.log("dataArray =" + dataArray)

return dataArray

}

Change following lines in the code based on your configuration. See below screenshot to get some idea where to get it.

var address = 'MyGatewayServerIpOrFullDomainName:5000'; // ZappySys Data Gateway Server Name / IP
var user = 'tdsuser'; // ZappySys Data Gateway User Name
var userPwd = 'MyTdsP@ssword123';  //Password of ZappySys Data Gateway User
var db = 'odata_native';

var address = 'MyGatewayServerIpOrFullDomainName:5000'; // ZappySys Data Gateway Server Name / IP

var user = 'tdsuser'; // ZappySys Data Gateway User Name

var userPwd = 'MyTdsP@ssword123'; //Password of ZappySys Data Gateway User

var db = 'odata_native';

Write AppScript (JavaScript Macros) to Import REST API in Google Sheet. Call JSON / XML /CSV / SOAP API. Read from JSON/ XML files.

Now Click anywhere in the Sheet1. Type below formula in the formula bar and hit Enter.

=ApiToGoogleSpreadSheet("select * from $")

1

=ApiToGoogleSpreadSheet("select * from $")

–OR–

=ApiToGoogleSpreadSheet("select * from $ WITH( Filter='$.value[*]', SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json' ) ")

1

=ApiToGoogleSpreadSheet("select * from $ WITH( Filter='$.value[*]', SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json' ) ")
That’s it. You should data populated like below in your Google Sheet.
Loading REST API data in Google Sheet. Read JSON / XML using AppScript Function.

REST API / XML SOAP Pagination Settings for Google Sheet AppScript Code

Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that ZappySys ODBC Driver includes many options to cover virtually any pagination method.

Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):

https://zappysys.com/blog/ssis-rest-api-looping-until-no-more-pages-found/

Paginate by Response Attribute

This example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.

SELECT * FROM $
WITH(
SRC=@'https://zappysys.com/downloads/files/test/pagination_nextlink_inarray_1.json'
,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']
)

Paginate by URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).

SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

Paginate by URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).

SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/cust-<%page%>.xml'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Paginate by Header Link (RFC 5988)

API like GitHub / Wordpress use Next link in Headers (RFC 5988)

SELECT * FROM $
LIMIT 25
WITH(
	 Src='https://wordpress.org/news/wp-json/wp/v2/categories?per_page=10'
	,PagingMode='ByResponseHeaderRfc5988'
	,WaitTimeMs='200' --//wait 200 ms after each request
)

REST API / SOAP Web Service Connection Settings for Google Sheet AppScript Code

If you need to authenticate or authorize your user to access a web resource, you will need to use one of the Connections:

HTTP
OAuth

ZappySys XML Driver - HTTP and OAuth Connection Types

HTTP Connection

Use HTTP Connection for simple Windows, Basic, NTLM or Kerberos authentication. Just fill in a username and a password and you are good to go!

You can also use HTTP Connection for more sophisticated authentication like:

SOAP WSS (when accessing a SOAP WebService)
Static Token / API Key (when need to pass an API key in HTTP header)
Dynamic Token (same as Static Token method except that each time you need to log in and retrieve a fresh API key)
JWT Token (As per RFC 7519)

OAuth

If you are trying to access REST API resource, it is a huge chance, you will need to use OAuth Connection. Read this article to understand how OAuth authentication and authorization works and how to use it (article originally was written for SSIS PowerPack, but the concepts and UI stay the same): https://zappysys.com/blog/rest-api-authentication-with-oauth-2-0-using-ssis

Other settings for REST API / SOAP XML Call in Google Sheet AppScript Code

There are few settings you can coder while calling Web API

API Limit / Throttling

While calling public API or other external web services one important aspect you have to check, how many requests are allowed by your API. Especially when you use API pagination options to pull many records you have to slow down based on API limits. For example, your API may allow you only 5 requests per second. Use Throttling Tab on Driver UI to set delay after each request.

2D Array Transformation

If you are using JSON or XML API Driver then possible you may have to transform your data using 2D array transformation feature. Check this link for more information.

REST API / XML SOAP Performance Tips for Google Sheet AppScript Code

While calling APIs you may face some performance issues. There are a few tips you can consider to speed up things.

Use Server-side filtering if possible in URL or Body Parameters

Many API supports filtering your data by URL parameters or via Body. Whenever possible try to use such features. Here is an example of odata API, In the below query the first query is faster than the second query because in the first query we filter at the server.

SELECT * FROM value
WITH(
	 Src='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json&$filter=Country eq ''USA'''
	,DataFormat='Odata'
)

-- Slow query - Client-side filtering
SELECT * FROM value
WHERE Country ='USA'
WITH(
	 Src='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json'
	,DataFormat='Odata'
)

Avoid Special features in SQL Query (e.g. WHERE, Group By, Order By)

ZappySys API engine triggers client-side processing if special features are used in Query. Following SQL Features will trigger Client-Side processing which is several times slower than server-side processing. So always try to use simple query (Select col1, col2 .... from mytable )

WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY
FUNCTIONS (e.g. Math, String, DateTime, Regex... )

LIMIT clause does not trigger client-side processing.

Consider using pre-generated Metadata / Cache File

Use META option in WITH Clause to use static metadata (Pre-Generated)There are two more options to speedup query processing time. Check this article for details.

select * from value WITH( meta='c:\temp\meta.txt' )
--OR--
select * from value WITH( meta='my-meta-name' )
--OR--
select * from value WITH( meta='[ {"Name": "col1",&nbsp;"Type": "String", Length: 100},&nbsp;{"Name": "col2",&nbsp;"Type": "Int32"} ...... ]' )

Enable Data Caching Options (Found on Property Grid > Advanced Mode Only )

Consider using Metadata / Data Caching Option

ZappySys API drivers support Caching Metadata and Data rows to speed up query processing. If your data doesn't change often then you can enable this option to speed up processing significantly. Check this article for details how to enable Data cache / metadata cache feature for datasource level or query level. To define cache option at query level you can use like below.

SELECT * FROM $
WITH 
(  SRC='https://myhost.com/some-api'
  ,CachingMode='All'  --cache metadata and data rows both
  ,CacheStorage='File' --or Memory
  ,CacheFileLocation='c:\temp\myquery.cache'
  ,CacheEntryTtl=300 --cache for 300 seconds
)

Use --FAST Option to enable Stream Mode

ZappySys JSON / XML drivers support --FAST suffix for Filter. By using this suffix after Filter driver enables Stream Mode, Read this article to understand how this works.

SELECT * FROM $ 
LIMIT 10 --//add this just to test how fast you can get 10 rows
WITH(
  Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
 ,SRC='https://zappysys.com/downloads/files/test/large_file_100k_largearray_prop.json.gz'
 --,SRC='c:\data\large_file.json.gz'
 ,IncludeParentColumns='False'  --//This Must be OFF for STREAM mode (read very large files)
 ,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files)
)

Calling SOAP Web Service in Google Sheet AppScript Code

To call SOAP API you need to know Request XML Body Structure. If you are not sure how to create SOAP Request body then no worries. Check this article to learn how to generate SOAP Request body using the Free tool SoapUI. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.

What is SOAP Web Service?

If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept about SOAP Web service standard from this link There are two important aspects in SOAP Web service.

Getting WSDL file or URL
Knowing exact Web Service URL

What is WSDL

In very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then analyze or sometimes you may get direct URL (e.g. http://api.mycompany.com/hr-soap-service/?wsdl )

Example SQL Query for SOAP API call using ZappySys XML Driver

Here is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding.

SELECT * FROM $
WITH(
	 Src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)

Now let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)

Video Tutorial - Introduction to SOAP Web Service and SoapUI tool

Before we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.

Using SoapUI to test SOAP API call / Create Request Body XML

Assuming you have downloaded and installed SoapUI from here, now we are ready to use WSDL for your SOAP Web Service Calls. If you do not have WSDL file or URL handy then contact your API provider (sometimes you just have to add ?wsdl at the end of your Service URL to get WSDL so try that. Example: http://mycompany/myservice?wsdl ). If you don't know what is WSDL then in short, WSDL is Web service Description Language (i.e. XML file which describes your SOAP Service). WSDL helps to craft SOAP API request Body for ZappySys XML Driver. So Let's get started.

Open SoapUI and click SOAP button to create new SOAP Project
Enter WSDL URL or File Path of WSDLFor example WSDL for our sample service can be accessed via this URL
```
http://www.dneonline.com/calculator.asmx?wsdl
```
Create new SOAP API Project in SoapUI tool for SOAP API Testing
Once WSDL is loaded you will see possible operations you can call for your SOAP Web Service.
If your web service requires credentials then you have to configure it. There are two common credential types for public services (SOAP WSS or BASIC )
1. To use SOAP WSS Credentials select request node and enter UserId, Password, and WSS-PasswordType (PasswordText or PasswordHash)
  
  Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
2. To use BASIC Auth Credentials select request node and double-click it. At the bottom click on Auth (Basic) and From Authorization dropdown click Add New and Select Basic.
  Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
Now you can test your request first Double-click on the request node to open request editor.
Change necessary parameters, remove optional or unwanted parameters. If you want to regenerate request you can click on Recreate default request toolbar icon.
Create SOAP Request XML (With Optional Parameters)
Once your SOAP Request XML is ready, Click the Play button in the toolbar to execute SOAP API Request and Response will appear in Right side panel. Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response)

Create DSN using ZappySys XML Driver to call SOAP API

Once you have tested your SOAP API in SoapUI tool, we are ready to use ZappySys XML driver to call SOAP API in your preferred BI tool or Programming language.

First open ODBC Data Sources (search ODBC in your start menu or go under ZappySys > ODBC PowerPack > ODBC 64 bit)
Goto System DSN Tab (or User DSN which is not used by Service account)
Click Add and Select ZappySys XML Driver ZappySys ODBC Driver for XML / SOAP API
Configure API URL, Request Method and Request Body as below ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body
(This step is Optional) If your SOAP API requires credentials then Select Connection Type to HTTP and configure as below.
ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
Configure-Request Headers as below (You can get it from Request > Raw tab from SoapUI after you test the request by clicking the Play button) Configure SOAP API Request Headers - ZappySys XML Driver
Once credentials entered you can select Filter to extract data from the desired node. Make sure to select array node (see special icon) or select the node which contains all necessary columns if you don't have array node. Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy)
If prompted select yes to treat selected node as Array (This is helpful when you expect one or more record for selected node) Treat selected node as XML Array Option for SOAP API Response XML

Preview SOAP API Response / Generate SQL Code for SOAP API Call

Once you configure settings for XML Driver now you can preview data or generate example code for desired language (e.g. C#, Python, Java, SQL Server). Go to Preview tab and you will see default query generated based on settings you entered in previous sections. Attributes listed in WITH clause are optional. If you omit attribute in WITH clause it will use it from Properties tab.

Preview Data

Preview SOAP API Response in ZappySys XML Driver

Generate Code Option

Generate Example Code for ZappySys Driver

Conclusion

In this article, we explored ideas about to loading data from virtually any REST / SOAP API into Google Sheet. We also discussed how to read from JSON file into Google Sheet. Using ODBC PowerPack you can integrate RESTful / JSON / XML /CSV data into your Google Sheet in minimum time. Download FREE trial and explore many other options not discussed in this article.