SQL Server Results (Table) -> JSON -> Google Sheet (multiple line items)

  • 14 January 2022
  • 1 reply
  • 328 views

Userlevel 1

Searched a lot for this but never found the end to end solution, so I’m sharing here. 

 

I have a case where I want to send an employee the results from a SQL Server query (table records) on a regular basis, but haven’t been able to piece it together until now. So here’s an overview I hope is useful for someone else out there:
 

  1. Trigger: Schedule by Zapier - running every week. 
  2. Action: SQL Server - Find row via Custom Query for the results you want
    (e.g. SELECT * FROM SALESORDER WHERE Employee=”Betty” For JSON AUTO, INCLUDE_NULL_VALUES).
    - The trick here is to put “FOR JSON AUTO, INCLUDE_NULL_VALUES” at the end of your query. This returns a single JSON field for Zapier as a result and includes Null values (in case you have blanks).
    - Note: there is a character limit on this JSON field from SQL Server (~for me it was 2000 characters) so beware this won’t work with large tableset results - you’d need to do multiple queries and concat them later in Zapier - the 2000 character limit was enough for ~10 records in my sample approach.
  3. Action: Code by Zapier: For this step you’ll need to run some simple Javascript to parse the JSON in multiple line items that can be understood by Zapier: 
    - Set up a Code by Zapier step using JavaScript
    - Set up your Input Data variable name as InputData and the value to be your JSON result field from Step 2.
    - Use the following code: 
    let smooth = JSON.parse(InputData);
    output = [{value: smooth}]

    - Test the Action and you should find that all the records are now mult-line item results for Zapier.
    Example:
    value:
              1: 
                   Name: Jon Smith
                   Email: jsmith@email.com
                   Amount: 10.00
              2: 
                   Name: Jane Smith
                   Email: jasmith@email.com
                   Amount: 60.00
              3: 
                   Name: Dane Young
                   Email: dyoung@email.com
                   Amount: 100.00
  4. Create a subsequent step that uses multi-line item output (e.g. a Google Sheet) you can now add multi-line item fields as per standard Zapier approach: https://zapier.com/help/create/basics/use-line-items-in-zaps
    I used a google sheet with fields Name, Email, Amount and placed the fields from Step 3 in them and all multiple returned records populate in the sheet.

TL;DR:

The two pieces of magic here are:

  1. Use “FOR JSON AUTO” at the end of a SQL Server statement to receive the results in a single JSON formatted field.
  2. Use Javascript JSON.parse(InputData); output = [{value: smooth}] to return the JSON in a multi-line item format Zapier can understand.

1 reply

Userlevel 4
Badge +6

Woah! Thanks so much for sharing @Frances! Absolutely love when Zapier users drop this kind of knowledge for the benefit of others. You’re amazing.

(And seriously. If you have any other nuggets of knowledge you’d like to share, please do so. That’s what this community is all about!)

Reply