{"id":5155,"date":"2018-10-09T22:52:37","date_gmt":"2018-10-09T22:52:37","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5155"},"modified":"2025-03-18T07:20:18","modified_gmt":"2025-03-18T07:20:18","slug":"import-rest-api-google-sheet-call-appscript-load-json-soap-xml-csv","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/import-rest-api-google-sheet-call-appscript-load-json-soap-xml-csv\/","title":{"rendered":"Import REST API in Google Sheet AppScript – JSON \/ SOAP XML \/ CSV"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Google Sheets Online<\/a> which makes it much simpler to\u00a0Read\/Write Google Sheets Data in ODBC<\/strong> 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.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n

\"\"<\/a>In our previous few articles<\/a> we saw various use cases of Data Gateway<\/a>.\u00a0 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<\/a> (i.e. Macro Code like in Excel but Google use JavaScript Language).<\/p>\n

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<\/a>\u00a0but 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.<\/p>\n

Requirements<\/h2>\n
    \n
  1. Make sure you have a valid Google account and access to Google Sheet Service<\/li>\n
  2. Download and Install ZappySys ODBC PowerPack<\/a><\/li>\n<\/ol>\n

    How does it work?<\/h2>\n

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

      \n
    1. first of all, install ZappySys ODBC PowerPack<\/a>\u00a0and make sure ZappySys Data Gateway is running (By default it should be).<\/li>\n
    2. 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<\/li>\n
    3. Google Server (i.e. AppScript) sends SQL query (for REST API Service \/ Files) using MSSQL JDBC Driver<\/strong> to ZappySys Data Gateway (TDS Protocol<\/a> used by JDBC Driver and ZappySys Data Gateway).<\/li>\n
    4. ZappySys Data Gateway Parses SQL Query request and translates to REST API call or File Read request<\/li>\n
    5. JSON \/ XML \/ CSV data is parsed into Rows and Columns and Sent back to Google Sheet Service<\/li>\n
    6. Your REST API \/ File data is rendered in rows \/ columns in Google Sheet<\/li>\n<\/ol>\n

      Step-By-Step : Import REST API in Google Sheet<\/h2>\n

      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.<\/p>\n

      Configure ZappySys Data Gateway<\/h3>\n
      Now let's look at how to configure\u00a0ZappySys Data Gateway<\/a>. 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<\/a> on various use cases of Data Gateway.\r\n

      Configure Data Gateway User \/ Port<\/span><\/h4>\r\nNow 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).\r\n
        \r\n \t
      1. Assuming you have installed\u00a0ZappySys ODBC PowerPack<\/a>\u00a0using default options (Which also enables Data Gateway Service)<\/li>\r\n \t
      2. Search \"Gateway\" in your start menu and click ZappySys Data Gateway\r\n
        \r\n\"Open<\/a>\r\n

        Open ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t

      3. First make sure Gateway Service is running (Verify Start icon is disabled)<\/li>\r\n \t
      4. Also verify Port on General Tab\r\n
        \r\n\"Port<\/a>\r\n

        Port Number setting on ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t

      5. Now go to Users tab. Click Add<\/strong> 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.\r\n
        \r\n\"Add<\/a>\r\n

        Add Data Gateway User<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n \r\n

        Configure Data Source<\/span><\/h4>\r\n
          \r\n \t
        1. After user is added, go to Data Sources tab. Click Add<\/strong>\u00a0icon 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.\r\n
          NOTE:<\/strong> Whenever possible use native driver option for better performance \/ security and ease of use.<\/pre>\r\n
          \r\n\"Add<\/a>\r\n
          Add Gateway Data Source (Native JSON Driver)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        2. 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.\u00a0 Enter following URL.\r\n
          https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Invoices?$format=json<\/pre>\r\n<\/li>\r\n \t
        3. You can also view response structure and select default hierarchy (i.e. Filter) like below (Select Array Icon) for data extraction.\r\n
          \r\n\"Configure<\/a>\r\n
          Configure JSON API Data source<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n
          Test SQL Query \/ Preview Data<\/span><\/h4>\r\n
            \r\n \t
          1. Now go to Preview Tab. You can click Preview button to execute default query\r\nOR\r\nSelect Table name from dropdown to generate SQL with column names.\r\n
            \r\n\"JSON<\/a>\r\n
            JSON \/ REST API Driver Query Preview \/ Query Examples (Read REST API or JSON Files)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          2. 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.\r\n
            \r\n\"Using<\/a>\r\n
            Using SQL Query Builder (For Files or REST \/ SOAP API - JSON \/ XML \/ CSV Format)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          3. 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.\r\n
            \r\n\"Generate<\/a>\r\n
            Generate Example Code for ZappySys Driver<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          4. Click OK<\/strong> to Close Data Source UI<\/li>\r\n \t
          5. Once data source is tested and configured you can click Save <\/strong>button in the Gateway UI toolbar and click Yes<\/strong> for Restart Service<\/strong>.<\/li>\r\n<\/ol>\r\n <\/div>\n
            Open Firewall Port<\/h3>\n
            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.<\/p>\n
            Call AppScript to load REST API data into\u00a0Google Sheet<\/h3>\n
            Once you done with Data Gateway configuration you can move forward to creating Google Sheet.<\/p>\n
              \n
            1. Create a new Google Sheet. Let’s call it Gateway Data<\/strong><\/li>\n
            2. From the tools menu click Script Editor
              \n<\/strong><\/strong>              \""<yoastmark\"<\/a><\/li>\n
            3. Write Following Code and Click Save\n
              function ApiToGoogleSpreadSheet(sqlQuery) {\r\n  if(sqlQuery==null){\r\n    \/\/Default Query\r\n    sqlQuery=\"select * from $ WITH( \\\r\nFilter='$.value[*]', \\\r\nSRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json' \\\r\n) \";\r\n  }\r\n    var address = 'MyGatewayServerIpOrFullDomainName:5000'; \/\/ ZappySys Data Gateway Server Name \/ IP\r\n    var user = 'tdsuser'; \/\/ ZappySys Data Gateway User Name\r\n    var userPwd = 'MyTdsP@ssword123';  \/\/Password of ZappySys Data Gateway User\r\n    var db = 'odata_native';\r\n    var msSqlUrlSyntax ='jdbc:sqlserver:\/\/'\r\n    var dbUrl = msSqlUrlSyntax + address + ';databaseName=' + db;\r\n    var conn = Jdbc.getConnection(dbUrl, user, userPwd);\r\n    var start = new Date();\r\n    var stmt = conn.createStatement();\r\n    \/\/stmt.setMaxRows(10);\r\n    var results = stmt.executeQuery(sqlQuery);\r\n    var rsMeta = results.getMetaData();\r\n    var numCols = rsMeta.getColumnCount();\r\n    \/\/var numRows = results.getFetchSize()\r\n    \/\/Logger.log(numRows)\r\n    var dataArray = [];\r\n    \/\/get column names\r\n    var headers = new Array(new Array(numCols));\r\n    for (var col = 0; col < numCols; col++){\r\n      headers[0][col] = rsMeta.getColumnName(col+1);\r\n    }\r\n  \r\n    \/\/resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);\r\n    \/\/rowString.push(results.getString(col + 1))\r\n   \r\n    dataArray[dataArray.length]=headers[0];\r\n    \r\n    while (results.next()) {\r\n        var rowString = [];   \r\n        rowString = [];\r\n\r\n        for (var col = 0; col < numCols; col++)     \r\n        {\r\n            rowString.push(results.getString(col + 1));\r\n        }\r\n\r\n        dataArray[dataArray.length]=rowString;\r\n    }\r\n\r\n    results.close();\r\n    stmt.close();\r\n\r\n    var end = new Date();\r\n    Logger.log('Time elapsed: %sms', end - start);\r\n\r\n     \/\/Logger.log(\"dataArray =\" + dataArray)\r\n    return dataArray\r\n \r\n}<\/pre>\n<\/li>\n
            4. Change following lines in the code based on your configuration. See below screenshot to get some idea where to get it.\n
              var address = 'MyGatewayServerIpOrFullDomainName:5000'; \/\/ ZappySys Data Gateway Server Name \/ IP\r\nvar user = 'tdsuser'; \/\/ ZappySys Data Gateway User Name\r\nvar userPwd = 'MyTdsP@ssword123';  \/\/Password of ZappySys Data Gateway User\r\nvar db = 'odata_native';<\/pre>\n
              \"Write<\/a>
              Write AppScript (JavaScript Macros) to Import REST API in Google Sheet. Call JSON \/ XML \/CSV \/ SOAP API. Read from JSON\/ XML files.<\/p><\/div><\/li>\n
            5. Now Click anywhere in the Sheet1. Type below formula in the formula bar and hit Enter.\n
              =ApiToGoogleSpreadSheet(\"select * from $\")<\/pre>\n
              –OR–<\/p>\n
              =ApiToGoogleSpreadSheet(\"select * from $ WITH( Filter='$.value[*]', SRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json' ) \")<\/pre>\n<\/li>\n
            6. That’s it. You should data populated like below in your Google Sheet.\n
              \"Loading<\/a>
              Loading REST API data in Google Sheet. Read JSON \/ XML using AppScript Function.<\/p><\/div><\/li>\n<\/ol>\n
              REST API \/ XML SOAP Pagination Settings for Google Sheet AppScript Code<\/h2>\n
              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\u00a0ZappySys ODBC Driver<\/em> includes many options to cover virtually any pagination method.<\/div>\r\n
              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):<\/span><\/div>\r\n
              https:\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/<\/a><\/div>\r\n
              Paginate by Response Attribute<\/h3>\r\nThis 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.\r\n
              SELECT * FROM $\r\nWITH(\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/pagination_nextlink_inarray_1.json'\r\n,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']\r\n)<\/pre>\r\n
              Paginate by URL Parameter (Loop until certain StatusCode)<\/h3>\r\nThis 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).\r\n
              SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'\r\n,PagingMode='ByUrlParameter'\r\n,PagingByUrlAttributeName='page'\r\n,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'\r\n,PagingByUrlCheckResponseStatusCode=401\r\n,IncrementBy=1\r\n)<\/pre>\r\n
              Paginate by URL Path (Loop until no record)<\/h3>\r\nThis 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).\r\n
              SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/cust-<%page%>.xml'\r\n,PagingMode='ByUrlPath'\r\n,PagingByUrlAttributeName='<%page%>'\r\n,PagingByUrlEndStrategy='DetectBasedOnRecordCount'\r\n,IncrementBy=1\r\n)<\/pre>\r\n
              Paginate by Header Link (RFC 5988)<\/h3>\r\nAPI like GitHub \/ Wordpress use Next link in Headers (RFC 5988<\/a>)\r\n
              SELECT * FROM $\r\nLIMIT 25\r\nWITH(\r\n\t Src='https:\/\/wordpress.org\/news\/wp-json\/wp\/v2\/categories?per_page=10'\r\n\t,PagingMode='ByResponseHeaderRfc5988'\r\n\t,WaitTimeMs='200' --\/\/wait 200 ms after each request\r\n)<\/pre>\r\n <\/div>\n
              REST API \/ SOAP Web Service Connection Settings for Google Sheet AppScript Code<\/h2>\n
              If you need to authenticate or authorize your user to access a web resource, you will need to use one of\u00a0the Connections:<\/em><\/div>\r\n