Workflow Sample: Episode 488: Collaboration with Cloud Accounting (Part 3)

Arranging a button for downloading CSV for MS Excel. (previous post)

From the aspect of a person who has to input data into an Accounting system, the "Download button" is very helpful. It allows to add so-called "Journal slip" data snappy to an Excel file. The business efficiency differs greatly depending on "there is" or "isn't" a button. The difference is to take only five minutes for inputting daily accounts receivable, or one whole hour.

However, it is yet a closed to personal process since it is an operation that "to append to an Excel file". The know-hows, which should be associated to the Step, such as "Where is the latest file?" or "Tips and knacks for the work" or "Technique for the case where minor modifications are required", these are tend to be individualism. (As well as anxieties for omission, or miss-copying or fraud.)

The following Workflow is a mechanism of auto-appending of multiple Journal slip data, which have been generated automatically, to Google SpreadSheet. (Either "MF Cloud-accounting" or "Freee",) throwing data to any of Cloud based accounting software, it is very convenient if "Journal slip" was managed uniformly on the Cloud.

By the way, in the first place, it should be the job for the Workflow system that to aggregate the issues which flowed on the Workflow. "List of Issues", for example, total and average of each property are aggregated. However,in a case where each Issue generates "uncertain number of slips", it is difficult to correspond the needs of demanding an aggregation on the "slips". Another table of "List of slips" should be prepared separately in such a case.

Here, we use the "Sheets API v4", which appeared in May 2016.

[Sales Report-SpreadSheet cooperation]

[Sales Report-SpreadSheet cooperation:1. Sales Report" screen]

It is tough to master APIs.

However, it will be a required skill for the most of the business people "to master APIs" in the near future, just like the way it is today that "Personal computer to be mastered". In fact, many of the people have already experienced API communication configuration between systems, such as Facebook and Twitter.

By the way, Google Sheets API v4 is significantly different from v3 in its design.

As it is describe by Google itself that
> The Sheets API gives you full control over the content and appearance of your spreadsheet data.

it allows to manipulate more detailed layouts. This means you are capable of "manipulation at various levels."

Whereas, it has also become cumbersome for users who want to use it simpler to seek necessary API information. It might be quicker in some cases referring to "Migrate to the Google Sheets API" when you see its manuals. By the way, "JSON-based REST API" is thorough in v4, so I have an impression that it is very easy to use in terms of productivity.

Since you can access Sheets APIs on roughly the three levels which

spreadsheets
spreadsheets.sheets
spreadsheets.values

In this Workflow example, Request of "AppendCells" is sent to "batchUpdate" of "spreadsheets"

= Setting sample OAth2 Communication with Google (Throwing Message Intermediate Event)

POST: https://sheets.googleapis.com/v4/spreadsheets/●{spreadsheetId}●:batchUpdate
Content-Type: application/json

Authorization Code Request URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
Access Token Request URL: https://accounts.google.com/o/oauth2/token
Scope: https://www.googleapis.com/auth/spreadsheets
Consumer Key: ※ Retrieve
Consumer Secret: ※ Retrieve

= Sample of Request Json to be generated
※ Embed AppendCellsRequest object into Requests object
※ Repeat the generating column data (RowData object)

{"requests": [{"appendCells": {"sheetId": 0,"rows": [ 
{"values": [ 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "2000-01-01"}}, 
    {"userEnteredValue": {"stringValue": "Account receivables"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Excluded"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"numberValue": 111}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Sales"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Sales including Tax 8%"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"numberValue": 111}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Title"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
],}, 
{"values": [ 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "2000-01-01"}}, 
    {"userEnteredValue": {"stringValue": "Account receivables"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Excluded"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"numberValue": 111}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Sales"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Sales including Tax 8%"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"numberValue": 111}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": "Title"}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
    {"userEnteredValue": {"stringValue": ""}}, 
],} 
],"fields": "*",},}],}

= Setting sample of Script Step "TSV to RequestJson" (Server-side JavaScript)

//// == Retrieving == 
var myTsv = data.get("6") + "";   
var sheetGid = data.get("9") + "";   


//// == Calculating == 
var myJson = '{"requests": [{"appendCells": {"sheetId": '  
             + sheetGid 
             + ',"rows": ['; 

var rowsArray = myTsv.split("\n");  // Separate at each linebreak, Store it to string[] array 
for (var i=0; i < rowsArray.length; i++){ 
  myJson += '{"values": ['; 
  myJson += '{"userEnteredValue": {"stringValue": ""}},';

  var cellsArray = rowsArray[i].split("\t");  // Separate at each Tab, Store it to string[] array 
  for (var j=0; j < cellsArray.length; j++){ 
    if( j == 5 || j == 11 ){      // 7th column(G column) and 13th column (M Column) are numerical 
      myJson += '{"userEnteredValue": {"numberValue": ' 
             + cellsArray[j]
             + '}},'; 
    }else{ 
      myJson += '{"userEnteredValue": {"stringValue": ' 
             + cellsArray[j] 
             + '}},'; 
    } 
  } 
  myJson += '],},'; 
} 
myJson += '],"fields": "*",},}],}'; 


//// == Updating == 
retVal.put("10", myJson );

[Data Items list]