How to Properly escape "'s in my Webhook Request Body Template?

AppSheet AppSheet Q&A
, ,
1

The error:

{
  "$type": "Nirvana.Data.TaskResultWebhook, V2API",
  "Headers": {
    "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.String, mscorlib]], mscorlib",
    "Authorization": "Bearer \" & [GET Token].[service.getAccessToken] & \"",
    "Accept": "application/json"
  },
  "Payload": "{\"Line\": [\"CustomerRef\": {\"value\": \"838\"}}",
  "Url": "https://sandbox-quickbooks.api.intuit.com/v3/company/*****************/invoice?",
  "Verb": "Post",
  "ContentType": "JSON",
  "MimeType": "application/json",
  "TimeoutSeconds": 180,
  "MaxRetryCount": 3,
  "AsyncExec": false,
  "AppErrors": {
    "$type": "Jeenee.DataTypes.AppErrors, Jeenee.DataTypes",
    "RecordInfo": false,
    "RecordWarning": true,
    "AnnotateErrors": false,
    "Errors": [
      {
        "$type": "Jeenee.DataTypes.AppError, Jeenee.DataTypes",
        "Severity": 0,
        "ErrorCode": "ERROR_WORKFLOW_WEBHOOK_BODY_INVALID",
        "Description": "Failed to parse JSON due to After parsing a value an unexpected character was encountered: :. Path 'Line[0]', line 1, position 23.. Invalid JSON value starts with: : {\"value\": \"838\"}}",
        "DetailedErrorMessage": null,
        "TargetField": null,
        "LearnMoreUrl": null,
        "ComponentDescriptor": {
          "$type": "Jeenee.DataTypes.AppComponentDescriptor, Jeenee.DataTypes",
          "ComponentName": "BEHAVIOR_WORKFLOW",
          "EditorPropertyId": "ErrorPaneHeader",
          "SchemaPath": null,
          "OpenSchemaPath": null
        }
      }
    ]
  },
  "TaskType": "Webhook",
  "TaskName": "POST_Invoice_QBO"
}

The template:

{
    "Line": [
    <<Start: [Related ServiceLOGs]>>
    {
        "DetailType": "SalesItemLineDetail",
        "Amount": <<DECIMAL([TSPrice])>>,
         "SalesItemLineDetail": {
            "ItemRef": {
                "name": "<<[Service]>>",
                "value": "<<NUMBER([SrvcID].[QBSrvcID])>>"
                }
            }
        }
    ],
    <<End>>
    "CustomerRef": {
        "value": "<<NUMBER([ShopID].[QBShopID])>>"
    }
}

The Observation:

// I used the generated template to format this part to find all the services related to the invoice and add the listed line items parameters to be posted to quickbooks.

I have an app script that can successfully perform this action but only handles one line item.

Using intuit developer api explorer i figured i should be able to accomodate all the services by going with the webhook and referencing related services with the <<Start: [Related Services]>> template expression to append the parameters in reference to each line item related to the invoice that is triggered when an assignment’s status is set to finalized.

I am positive it is just in how the " mark is not being escaped correctly on my end but I thought i was doing it correctly as described in Call a webhook from an automation

[MALFORMED]
// AppSheet is adding in \ causing a syntax error to trigger and is ignoring the <Start:End> Template expression to loop and populate the related services (line items) parameters i need to extract in order to successfully POST and invoice to QuickBooks.

"Payload": "{\"Line\": [\"CustomerRef\": {\"value\": \"###\"}}",​

[ORIGINAL]

"Payload":{
  "Line": [
<<Start: [Related ServiceLOGs]>>
  {
     "DetailType": "SalesItemLineDetail",
     "Amount":<<DECIMAL([TSPrice])>>,
     "SalesItemLineDetail":{
        "ItemRef":{
           "name":"<<[Service]>>",
           "value":"<<NUMBER(SrvcID].[QBSrvcID])>>"
           }
        }
     }
<<End>>
  ],
  "CustomerRef":{
    "value":"<<NUMBER([ShopID].[QBShopID])>>"
  }
}​

[DESIRED TEMPLATE TRANSLATION FOR POST PAYLOAD (REQUEST BODY)]

"Payload": {
"Line":[
    //Line Item1
    {"DetailType":"SalesItemLineDetail","Amount": $$$.$$,
    "SalesItemLineDetail":{"ItemRef":{"name":"FOO","value":"###"}}},
    //Line Item2
   {"DetailType":"SalesItemLineDetail","Amount":$$$.$$,
    "SalesItemLineDetail":{"ItemRef":{"name": "BAR","value": "###"}}}],
"CustomerRef":{"value": "###"}
}
2

Your closing square bracket should be after your <>

Marc_Dillon_0-1681308476756.png
Marc_Dillon_0-1681308476756.png523×350 7.12 KB

2 Likes
3

face palm. ill update and see if it will go through. thx for the good eye.

4

It is just saying the webhook body is empty now but no more syntax errors…

{
  "$type": "Nirvana.Data.TaskResultWebhook, V2API",
  "Headers": {
    "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.String, mscorlib]], mscorlib",
    "Authorization": "\"Bearer ",
    "Accept": "application/json"
  },
  "Payload": "",
  "Url": "https://sandbox-quickbooks.api.intuit.com/v3/company/xxxxxxxxxxxx/invoice?",
  "Verb": "Post",
  "ContentType": "JSON",
  "MimeType": "application/json",
  "TimeoutSeconds": 180,
  "MaxRetryCount": 3,
  "AsyncExec": false,
  "AppErrors": {
    "$type": "Jeenee.DataTypes.AppErrors, Jeenee.DataTypes",
    "RecordInfo": false,
    "RecordWarning": true,
    "AnnotateErrors": false,
    "Errors": [
      {
        "$type": "Jeenee.DataTypes.AppError, Jeenee.DataTypes",
        "Severity": 0,
        "ErrorCode": "ERROR_WORKFLOW_WEBHOOK_BODY_EMPTY",
        "Description": "The webhook body is empty",
        "DetailedErrorMessage": null,
        "TargetField": null,
        "LearnMoreUrl": null,
        "ComponentDescriptor": {
          "$type": "Jeenee.DataTypes.AppComponentDescriptor, Jeenee.DataTypes",
          "ComponentName": "BEHAVIOR_WORKFLOW",
          "EditorPropertyId": "ErrorPaneHeader",
          "SchemaPath": null,
          "OpenSchemaPath": null
        }
      }
    ]
  },
  "TaskType": "Webhook",
  "TaskName": "POST_Invoice_QBO"
}

"Payload": {
    "Line": [
<<Start: [Related ServiceLOGs]>>
       {
            "DetailType": "SalesItemLineDetail",
            "Amount": <<[TSPrice]>>,
            "SalesItemLineDetail": {
                "ItemRef": {
                    "name": "<<[Service]>>",
                    "value": "<<[QBOSrvcID]>>"
                }
            }
        }
    <<End>>],
    "CustomerRef": {
        "value": "<<[ShopID].[QBOShopID]>>"
    }
}
5

Your body template does not need the “Payload”: portion.

I’m not sure where you got that top result from. Use the testing page to see what body/payload is actually generated by the template.

6

yeah i figured that out right before you replied lol. it was saying there was an invalid character at line[0] part 7 of line 2 or something along those lines and i just followed where it was telling me to go. it cleared up that error but now im kind of back where the original question started.

"$type": "Nirvana.Data.TaskResultWebhook, V2API",
  "Headers": {
    "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.String, mscorlib]], mscorlib",
    "Authorization": "\"",
    "Accept": "application/json"
  },

this is my authorization header theat references a return from a QuickBooks approved oauth script and it is using the approved library for the use case. i tested the callback and return in appsheet and it is all working properly but i dont seem to be able to get these ‘"’ right and looks like it is stopping before it even writes out Bearer in the quoted Authorization string much less making it to the access Token provided using the returns reference expression.

Authorization: '"''Bearer ' &  [GET Token].[service.getAccessToken]''"'
7

I’ve never even thought to use any expression in the headers config, interesting!

I did some experimenting, and it’s very odd how it works*, but I found that this format seems to work:

Authorization:"Basic " & [script].[val]

Or translated to your case:

Authorization:"Bearer " & [GET Token].[Service.getAccessToken]

*Edit: I just realized how it works (or at least I think). If you leave out the colon, it throws an error to that effect. Meaning its expects a certain structure in that field: a header name, followed by a colon, then followed by a value. That value can be generated by an expression. Typically, that expression is simply a quoted text string. The quotes you use for that, are not the final quotes in the generated header, even though from test page they look the same and are in the same spot. (check out the header result in the monitor instead, it shows a slightly different end result) Basically, it’s automatically assigning the quotes where needed around the header/value, you don’t need to provide them. So you just need to generate the value, which is the hard-coded text string "Bearer " concatenated with the script’s return value.

1 Like
8

So i was able to get the process to work but i soon noticed i would have to code to accommodate multiple line items that were dynamic in the number needed from invoice to invoice. I did get the automation to function, but only works on the first service associated with the assignment. just not skilled enough to know how to automate the codes structure to generate code blocks based on how many line items are associated with the assignment that triggers on status = finalized. so i just got my company to invest in zapier to trigger off spreadsheet update and used their ui to fill in the variables populated by the app to a individual QB sheet that has all the required fields as column headers [aka request headers] to call on once the zap is triggered to use to build an invoice, email to respective parties, and log activity in BigQuery. But to give a huge thank you to Marc for by all rights helping me figure out this problem that i can definitely use many times over in future app script api automations.

1 Like
9

This is a working app script to call QuickBooks API, Successfully Perform OAuth 2.0 Token exchange/ auth redirect, construct payload parameters in AppSheet, push parameters to App Script, Handle the Callback, but i was only able to handle one line item which isn’t much help when dealing with up to 10 to 15 tied to an assignment. I ended up using zapier to easy mode it but i worked very diligently on putting this together and hope someone can find a little insight into the process and how to work with QBO Intuit API Platform. thx again marc for helping me with my "e"sc/‘/’@ping handicap…

// Make sure you include the trailing _ to ensure your credentials do not show in any 
// logging or urls when running you script.
var COMPANY_ID_ = xxxxxxxxxxxxxx;
var CLIENT_ID_ = xxxxxxxxxxxxxxx;
var CLIENT_SECRET_ = XXXXXXXXXXXXXXXXXXXXXX;

/**
 * Authorizes and makes a request to the QuickBooks API. Assumes the use of a
 * sandbox company.
 */
  // The listed parameters are values populated by my automation in my appsheet and 
  // configured as key:values that can be configured once you select call a script bot 
  // process if you have them listed in you script code prior to adding it to appsheet. 
  // it will display the parameters and from there you can use expressions or string 
  // values to provide your script with needed parameters and protect sensitive 
  // information with 
  // layers>>----v----------v-----------v--------------v.
function run(amount, itemRefName, itemRefValue, customerRefValue ) {
  var service = getService_();
  if (service.hasAccess()) {
    // Get the Company ID to be used in the request.
    var companyId = COMPANY_ID_;
    //Build the payload used to POST the info needed to create and invoice in QBOm.
    const payload = {
      "Line": [
        {
          "DetailType": "SalesItemLineDetail",
          "Amount": amount,
          "SalesItemLineDetail": {
            "ItemRef": {
              "name": itemRefName,
              "value": itemRefValue
            }
          }
        }
      ],
      "CustomerRef": {
        "value": customerRefValue
      }
    };
    Logger.log(payload);
    var url = 'https://sandbox-quickbooks.api.intuit.com/v3/company/' +
      companyId + '/invoice?';
    var options = {
      'method': 'post',
      'payload': JSON.stringify(payload),
      'contentType': 'application/json',
      'headers': {
        Authorization: 'Bearer ' + service.getAccessToken(),
        Accept: 'application/json'
      },
      muteHttpExceptions: true
    };
    var response = UrlFetchApp.fetch(url, options);
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
    return response;

  } else {
    // If not authorized, get authorization URL.
       // I setup an HTML embedded view in the app to handle the auth approval screen 
       // using the referenced redirect as a return value and had appsheet display it in 
       // the app instead of redirecting outside the app. For my company it was a 
       // security measure. May be over-cautious but works :).
    var authorizationUrl = service.getAuthorizationUrl();
    // View the Log to obtain the URL.
    Logger.log('Open the following URL and re-run the script: %s',
      authorizationUrl);
    return authorizationUrl;
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  var service = getService_();
  service.reset();
}

/**
 * Configures the service.
 */
function getService_() {
  return OAuth2.createService('Quickbooks')
    // Set the endpoint URLs.
    .setAuthorizationBaseUrl('https://appcenter.intuit.com/connect/oauth2')
    .setTokenUrl('https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer')
    // Set the client ID and secret.
    .setClientId(CLIENT_ID_)
    .setClientSecret(CLIENT_SECRET_)
    // Required, set to Accounting for this example,
    // see QB developer portal for additional options.
    .setScope('com.intuit.quickbooks.accounting')
    // Set the name of the callback function in the script referenced
    // above that should be invoked to complete the OAuth flow.
    .setCallbackFunction('authCallback')
    // Set the property store where authorized tokens should be persisted.
    .setPropertyStore(PropertiesService.getUserProperties());
}

/**
 * Handles the OAuth callback.
 */
function authCallback(request) {
  var service = getService_();
  var authorized = service.handleCallback(request);
  if (authorized) {
    // Save the Company ID in the service's storage.
    service.getStorage().setValue('QuickBooks.companyId',
      request.parameter.realmId);
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied');
  }
}
1 Like