Using Google Workspace SDK to process purchase ord...

sankalpmalhotra · ‎11-14-2022

Use Case: Using Google Workspace SDK to process purchase order information and create a sales order in SAP S/4HANA Cloud.

Prelude:

SAP Build Process Automation enables automation of google workspace products such as Gmail, Google drive, Google calendar, Google sheet, Google slides, Google documents and Google cloud storage. In addition to that it has the capabilities to automate ML based services such as, Google document AI and vision AI. The below SDKs can be used in automations to automate Google services:

Google Authorization SDK

Google Workspace SDK

Google Cloud Storage SDK

Google Document AI SDK

Google Vision AI SDK

In this blog I will use Google Authorization SDK and Google Workspace SDK to talk about how you can automate google applications such as google drive, Gmail and google sheet. I am using Sales Order creation use case in SAP S/4HANA System to explain these features.

Detailed Use case for our automation:

Uses Google Workspace SDK to Search GMAIL account for Purchase Order Emails

Uses Google Workspace SDK to Read the email and download the attachments

Use SAP DOX service to read PDF and create an output schema

Uses Google Workspace SDK to Fill values from PDF into a pre-created Google Sheet

Uses Google Workspace SDK to Read values from Google Sheets to create Sales Order

Uses Pre-built SAP Sales Order Creation Bot to create Sales Order using the above values

Uses Google Workspace SDK to Reply to the original email with the created Sales Order ID

Recommended SDK's version: 1.31.49

Pre-requisites:

A Purchase Order in PDF Format

An Email in a GMAIL Account containing a PO (Purchase Orders) in the above format

A Sales Order Input template in Google Sheets

Sales Order Creation Automation that takes input from the above template

I have a prebuilt sales order automation bot and I am using the same. The bot expects a Data structure as shown below and gives Standard Order id as an output.

Sample expected Input by the prebuilt Sales Order Automation

Step – 1: Create a new Document Template as shown in the figure below and define the schema for the same document template to be used in the subsequent activities

Sample Purchase Order document Template

Step – 2: I will use a pre-defined template, for which I am populating the data read from purchase order and upload this to Google Drive for the BOT to use.

Sample sales order input template

Now, we are ready with the pre-requisite steps. Let us now start building our Automation.

Step – 3: Create a new automation to read Purchase Order data from Google Products and Add Google Packages as dependencies in this Automation.

New Automation

Now Select Dependencies tab and click on Add Dependency, then click on Add a Business Process project Dependency and from the drop down, select Google Authorization SDK & Google Workspace SDK.

Step – 4: Add following steps into the automation to Search Purchase Order Email and extract data from the attached PDF.

Our automation will search for an email in the users GMAIL account based on query input and then read the email contents. It will download the attachments to a local File System and then extract Data from the PDF Attachment to fill into Sales Order Template.

Then, the Bot will use the data from the template to create Sales order using our pre-built Sales order Automation

Add Google Authorization activity with proper scopes. For Google Sheets, the Google Drive scope is sufficient. [click here to refer Authorization Blog for more details]

Add Search Email activity to search for Purchase Order Email. This activity uses GMAIL API to search for emails based on the search filters as specified in the search query Parameters. You can optionally also specify if the search should include spam and trash folders or not. Output of this activity is a list of Metadata of the emails that match search criteria.

Add Read Email activity to read the contents of the email. This activity needs Message Id and a location to download media attachments (if any). Using this activity, you can download the attachments of an email into your local file system, or you can upload the attachments to Google Drive. We can use the output of Search activity (previous step) to give messageId input of this activity. Output of this activity is the content and details of the targeted message.

Add Extract Data activity to extract the Purchase Order details from the PDF attachment found in the previous step.This activity needs a Document Schema and a Document Template which we created in our pre-requisites. It will also need a document path which we can fetch from our previous Read Email step as shown in the fig. The output of this activity is the extracted data as per the schema defined in the Prerequisite document template.

Now our 1^st use case of extracting Purchase Order data out of user’s Email is complete. In the next step, we will use this data to fill the Sales Order template that is required by our Pre-built Sales Order Bot.

Add a Custom Data Type Variable of type any and initialize it with an empty list. As shown in the figure. This is a custom data type which will be used as an array of string arrays (2D string array) for input to the set activity of Google Sheets in the further steps.

Add a foreach loop to iterate on the extracted Data’s line Items (output of 4^th Activity)

Add activity inside for loop to Add Item (List) to add an item in the above data structure variable. Set the value as [currentMember.materialNumber.value, Step6.currentMember.quantity.value]Here we create the row values as to be inserted in the template in a string array (representing 1 row of the spreadsheet)

Add Set Cell Values (Google Sheet) activity to push values from our data structure variable into Google spreadsheet template for our Sales Order bot.

Add output parameter ThreadId in the automation for passing the Gmail thread Id for further steps. Map this output parameter with the thread Id which is output of Search Email activity.

With this step, we have an automation to search for an email and extract purchase order data from it using Google Workspace SDK and fill it in a Sales Order Template in Google spreadsheet residing in G-Drive.

Now, Lets make an automation that reads the data from the sales order template and creates a data model which is required by our pre-built Sales order automation.

Step – 5: Create a new Automation and add following steps into the automation to read Sales Order information from a template and trigger Sales order Creation bot.

Add a Get Cell Values (Google sheet) activity to fetch Header-Data values of the template. This activity expects Range and Spreadsheet ID as mandatory inputs. In order to get spreadsheet Id of our targeted template, open it in Google drive and copy the id from the Web URL as shown in the figure below. For more information on Spreadsheet Range and A1 notation refer Range- Further ReadThe output of this activity is a 2-dimensional Array of string which can be row major (i.e., each list of string represents a Row in spreadsheet), or it can be Column Major (i.e., each list of string represents a Row in spreadsheet) based on the input against the majorDimension input in the activity.

Add a Get Cell Values (Google sheet) activity to fetch Item-Data values of the template.

Create a Custom Script to map the values into the declared custom variable as shown below. Edit the script and add the below mapping code into the script. This custom script maps the data read from sales order sheet through the above activities and fills into the data model type as expected by the Sales order creation prebuilt bot.
Sample Script:{
"SalesOrderType": "OR",
"SalesOrganization": "1010",
"DistributionChannel": "10",
"Division": "00",
"SoldToParty": "10100007",
"OrderReason": "002",
"Items": [
{
"Product": "TG11",
"RequestedQuantity": "11"
},
{
"Product": "TG12",
"RequestedQuantity": "12"
}
]
}

Call the pre-built automation with the output of the custom script above

Add output parameter in the automation and map the output of the pre-built automation step.

With this, we now have an automation that can read sales order data from a Google Spreadsheet and create a sales order with this data in the SAP S/4HANA cloud.

In an ideal state, these two bots can run as trigger to keep searching for emails and creating sales order with the requested purchase order.

Now, lets create a master automation and reply to the email with the sales order Id that we get as an output of the previous automation.

Step – 6: Create a master automation to test and demo our bots. Add the below steps in this automation

Add "Extract Purchase Order From Google Workspace" automation as created in step 4 above.

Add "Create Sales Order using template" automation as created in step 5 above.

Add Reply All (Gmail) activity and add custom input data as below in the reply parameter input.

With this our demo use case is complete, we can run the master automation to test and create a Sales Order in SAP S/4HANA cloud using Google workspace SDK to extract purchase order Information and then replying to the customer with the created Standard Order ID.

Running the master automation:

On executing the master automation that we just created, an email reply is done by the automation to the requestor's email on the same thread, as shown in the figure below.

Successful Response on execution

Conclusion : So finally, when we run this master automation, all the values will be dynamically fetched from the Google Workspace products (sheets, gmail, calendar, slides, docs, etc.). this information will be processed using Google Workspace SDK and a Sales Order will be created in SAP S/4HANA Cloud Application, also the requester will be replied back with the sales order Id.

Suggested Further Reads: