Note, GetJson command has been introduced in xMart 4.5, it was not available before February 2020

Step 1: Generate GoogleSheet API key

If you want to use the latest API (v4), you’ll need to do the following:

  • Generate a spreadsheet API key (see instructions below).
  • Make your sheet publicly accessible.
  • Use a request of the form:

https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY

You’ll then get a clean JSON response back:

{
  "range": "Sheet1!A1:D5",
  "majorDimension": "ROWS",
  "values": [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "30/20/2016"],
    ["Totals", "$135.5", "7", "3/20/2016"]
  ]
}

Note that if you want to specify the entire contents of a page, an identifier such as Sheet1 is perfectly valid.

See Basic Reading for more information.

As of v4 API, all requests must be accompanied by an identifier (e.g. API key):

Requests to the Google Sheets API for public data must be accompanied by an identifier, which can be an API key or an access token.

Follow the steps in the linked document to create an API key on the credentials page.

Make sure to:

  1. Create a new app on Google Cloud Platform.
  2. Create a new API key.
  3. Add the Google Sheets API. (API Manager > Dashboard > Enable API)

Source: https://stackoverflow.com/a/44479726/1835270

Step 2: Edit xMart pipeline

Create or edit your pipeline https://extranet.who.int/xmart4/YOUR_MART/pipelines/PIPELINE_CODE In the Extract section, insert this command

<GetWebService TableName="Data" Url="https://sheets.googleapis.com/v4/spreadsheets/1z2hDygMzRG_yN5JBFVwGr4aci87AHSg1M7qvVu-uv_s/values/Sheet1!A1:D5?key=API_KEY">
  <GetJson>
      <PathHeader>values[0][*]</PathHeader>
      <Path>values[1:]</Path>
   </GetJson>
</GetWebService>

When the PathHeader element is defined, xMart will assume the data is in array format like shown above, rather than a regular JSON object with named properties like {id: 5}.

You can replace the Url and the Path element by the actual API endpoint to be called. This Url demoed here is the first example from Useful resources.

In the Url attribute, the & characters should be xml-escaped by using &amp; instead, else a parse error like '=' is an unexpected token. The expected token is ';' will be thrown.

Finish your pipeline by loading stage table data in your target table using the regular <LoadTable SourceTable="data" TargetTable="...">.

You’re done!

Useful resources

Google Console

https://console.developers.google.com/apis/api/sheets.googleapis.com/overview

GetWebService command

https://extranet.who.int/xmart4/pipeline-script-guide#GetWebService

GetJson command

https://extranet.who.int/xmart4/pipeline-script-guide#GetJson