Workflow Sample: Episode 550: Automation for the Task of Spreadsheet Filling in

The Task: Weekly sales report

I have each store manager report sales of the week.

It sure is good to operate a simple "Sales report Process", which is

Each and every "Store manager" makes reporting
"Director" at the headquarter confirms

However, those store managers seem to be working only "watching their boss".

I suppose it could be better, for example, to produce "more chances to access data of other store".

If you are using G suite, "Google SpreadSheet" is available to edit the same document by all store managers (* up to 50 people). And it will reduce labor for "summing up" of "Headquarter director". Regarding "mistakes on input", store manager who made mistake could aware of it by him or herself for comparing with data of other stores. Or, maybe managers would mention about "input mistakes" each other! (It may lead to activate communications between stores, and that leads improving productivity...)

Challenge: Preparation of the new SpreadSheet is troublesome

However, then, someone is needed to prepare "Spreadsheets for reporting" every week.

It would be too much for "directors" in headquarter to ask to do. They are hard on others, and easy on themselves. Like, they never have tried to be in time for deadline even for claiming out-of-pocket expense. Hmm, the biggest challenge seems to be the first Step at which "to prepare a new SpreadSheet and announce it to every store managers every week precisely".

[Weekly Sales Report]

Solution: Automatic generation of new SpreadSheet

By replacing the top Step with "Timer Start Event", automatic start of Workflow can be realized.

The Business Process Definition introducing here, automatically starts Workflow at 6:30 on Sunday morning. (The values such as "Template Sheet", "Save destination folder", "File name" are automatically set as well.) On the downstream side, there is an automatic processing Step called "Sheets Copy" and "Store manager notification", an Email throwing event. In most of the cases, new files will be prepared unmanned, announced to all store managers by unmanned. (Access URL to newly generated document will be notified automatically.)

In the automatic Step "Sheets Copy", the file (template Sheet) in Google Drive is referred to and the new spreadsheet is automatically saved in the folder for weekly sales report. No human being involved.

* There is a human Step called "1. E-mail text edit" on the way, but this Step will automatically flow to the downstream Steps after "being remained for 3-hour". If there is any communication matter, you edit the "Notification email text" at this Step.

Discussion: Difficulty of automating tasks

The automatic Step of "Sheets Copy" in this Business Process is Questetra's extended function (Service Task add-on).

* Import the Service Task definition file, "Google Drive file copy" (Addon-XML), in advance.

When designing a Business Process, set up "Communication permission setting" and "presence or absence of Team Drive access". Also specify the Data Items where "Original file", "Destination folder", "File name" is stored respectively. Even though the part of secure communication using OAuth 2 may be somewhat difficult, no programming knowledge is necessary for that setting. So anyone can build an automation environment.

＝OAuth 2.0 communication permission setting＝

1. Setting on Google Developers Console
Access to Dashboard, enable "Google Drive API" and get "Client ID" and "Client secret".

Name: (arbitrary, e.g. "q2drive")
Application type: Web application
Authorized JavaScript origins (No need to set)
Authorized redirect URI: https://s.questetra.net/oauth2callback

2. Communication setting of Questetra Workflow App
Set up [OAuth 2.0 Setting] and get refresh token

Name: (arbitrary)
Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force (Google standard)
Token Endpoint URL: https://accounts.google.com/o/oauth2/token (Google standard)
Scope: https://www.googleapis.com/auth/drive
Client ID: (which have obtained at Google Developers Console)
Consumer Secret: (which have obtained at Google Developers Console)

In addition, you can also make "Service Task add-on" your own. (In this case programming knowledge is required: M416)

In this example, "File duplication" is automated by request communication (OAuth 2 communication Drive API" v3 (Files Resource). By modifying this definition file, you can enhance it to an automatic Step that can specify multiple parent folders, or you can even create auto-step to control services such as Google Calendar, Salesforce, Dropbox.

#Sever side JavaScript in OAuth2 communication (Excerpt)

// preparing for API Request 
var apiRequest = httpClient.begin(); // HttpRequestWrapper 

// preparing for API Request (OAuth2 Access Token) 
var token = httpClient.getOAuth2Token( oauth2 ); 
apiRequest = apiRequest.bearer( token ); 

// preparing for API Request (Path parameters) 
var apiUri = "https://www.googleapis.com/drive/v3/files/" + fileId + "/copy"; 

// preparing for API Request (Query parameters) 
if( supportsTeamDrives === "true" ){ 
apiRequest = apiRequest.queryParam( "supportsTeamDrives", true ); 
} // API default 'false' 

// preparing for API Request (JSON Body) 
var requestObj = {}; 
requestObj.parents = []; 
requestObj.parents[0] = parent; 
requestObj.name = name; 
apiRequest = apiRequest.body( JSON.stringify( requestObj ), "application/json" ); 

// POST Request to the API 
var response = apiRequest.post( apiUri ); // HttpResponseWrapper 
var statusCode = response.getStatusCode() + "";

[Weekly Sales Report:"1. E-mail text edit" screen]