Workflow Sample: Episode 493: Managing Uses of Team Password (Sheets API v4)

Day-to-day work is full of Team passwords...

When you install a wifi router, you configure "Admin password". When you apply for online printing service, you register a "Business account". When you just buy a scanner, you are demanded "User registration". Despite I have grown up having been taught "Never share your password" since my childhood, there are lots of passwords that are inevitable to share in the real adult life! (And, top secret files are stored in the file server of the company...)

The following Workflow is a Business Process for managing "Team password".

You may regard it as a "Password manager" of an organization. It is devised to disclose a password after ten minutes when an employee makes a request. All the information, such as When it was used, Who used it, and Which password, will be recorded. (Moreover, "Password change" by the administrator will be recorded, as well.)

[Password Request]

When you run this Business Process, employees who are authorized to make a request will be able to obtain the password at any time. So to speak, it is a "very loose management". (In addition, the password itself is stored in clear-text.)

It can be said that it is suitable for Team password management in which less "confidential" and less "important", and regularly be changed, such as

"Security key" for WiFi
"Basic Authentication" for a Website
"Temporary browsing authorization" for cloud accounting

It shouldn't be used for the management of highly confidential Team password. (Rather, you shouldn't use Team passwords for such a system.)

By the way, "managing in clear\text" is not a method to be condemned altogether, for there might be a risk that excessive encrypting makes the user to be unable to decode. Also, it is excellent for storing the password in "Viewing restricted Google SpreadSheet" (an external system), aside managing in clear-text. Especially, if all the file browsing histories are managed in the Google Apps Vault, it can be said that it is a very effective means.

(As a matter of course, further sophistication can be considered, such as encrypt and store, or dividing the password to "section A" and "section B")

[Password Request: "1. Password Request" screen]

= Preparation of Google Spreadsheet to store Passwords
* Accessible via API only

1. Create new Google Spreadsheet
2. Confirm no authorized viewer exists in Shared setting
3. Write down Spreadsheet ID
4a. Put the Sheet name into [Choice Label] of "Target System"
4b. Put the Sheet id into [Choice ID] of "Target System"
5. (Add Sheets according to the number of Choices)

= Sample Setting for OAuth2.0 Communication authentication
== 1. Setting on Googlea Developers Console ==

Application type: Web application
Credentials: qapp
Authorized redirect URI: https://s.questetra.net/oauth2callback

== 2. Setting on Questetra HTTP Event (Client) ==

Name: sheets-api-v4
Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
Token Endpoint URL: https://accounts.google.com/o/oauth2/token
Scope: https://www.googleapis.com/auth/spreadsheets
Consumer Key: (Retrieve at Google Developers Console)
Consumer Secret: (Retrieve at Google Developers Console)

= Sample Setting for Script Step "Get URL" (Server side JavaScript)

//// == Retrieving == 
var mySystems = data.get("q_systems"); 
var spreadSheetId = data.get("q_spreadsheetid") + ""; 
var sheetId = mySystems.get(0).getValue() + ""; 
var sheetName = mySystems.get(0).getDisplay() + ""; 

//// == Calculating == 
var myUrl = ""; 
myUrl = 'https://sheets.googleapis.com/v4/spreadsheets/'; 
myUrl += spreadSheetId; 
myUrl += '/values/'; 
myUrl += sheetName; 
myUrl += '!C1:D1'; 

//// == Updating == 
retVal.put("11", myUrl );

▼Get PW via Google Sheets API v4

Method: GET
Access URL: #{data['●']}
Response: On
Connection: OAuth 2.0

== Response JSON sample ==

{
  "range": "wifi1!C1:D1",
  "majorDimension": "ROWS",
  "values": [
    [
      "example1@example.com",
      "qwerty"
    ]
  ]
}

= Sample Setting for Script Step "Response handling" (Server side JavaScript)

//// == Retrieving == 
var myJson = data.get("13") + ""; // JSON text 

//// == Calculating == 
var newIdPw = JSON.parse( myJson ); 

//// == Updating == 
retVal.put("5", newIdPw.values[0][0] ); 
retVal.put("6", newIdPw.values[0][1] );

= Sample Setting for Script Step "Request generation" (Server side JavaScript)

//// == Retrieving == 
var newId = data.get("q_newid") + ""; 
var newPw = data.get("q_newpw") + ""; 
var mySystems = data.get("q_systems"); 
//var spreadSheetId = data.get("q_spreadsheetid") + ""; 
var sheetId = mySystems.get(0).getValue() - 0; 
//var sheetName = mySystems.get(0).getDisplay() + ""; 

//// == Calculating == 
var dateTimeSerialNumber = Date.now() / 1000 / 60 / 60 / 24 + 70 * 365 + 19; 
// UNIX time to Excel-Lotus Serial Number 
// from December 31st 1899 

var myObj = {};  
myObj.requests = []; // Array  

myObj.requests[0] = {};  
myObj.requests[0].appendCells = {};  
myObj.requests[0].appendCells.rows = []; // Array  
myObj.requests[0].appendCells.sheetId = sheetId;  
myObj.requests[0].appendCells.fields = "*";  
myObj.requests[0].appendCells.rows[0] = {};  
myObj.requests[0].appendCells.rows[0].values = []; // Array 

myObj.requests[0].appendCells.rows[0].values[0] 
  = {"userEnteredValue": {"numberValue" : dateTimeSerialNumber } }; 
myObj.requests[0].appendCells.rows[0].values[1] 
  = {"userEnteredFormat": {"numberFormat": { "type": "DATE_TIME" } },
     "userEnteredValue": {"numberValue": dateTimeSerialNumber }}; 
myObj.requests[0].appendCells.rows[0].values[2] 
  = {"userEnteredValue": {"stringValue": newId }}; 
myObj.requests[0].appendCells.rows[0].values[3] 
  = {"userEnteredValue": {"stringValue": newPw }}; 

myObj.requests[1] = {};  
myObj.requests[1].sortRange = {};  
myObj.requests[1].sortRange.range = { "sheetId": sheetId,"startRowIndex": 0,"endRowIndex": 3 }; 
myObj.requests[1].sortRange.sortSpecs = [{"dimensionIndex": 0,"sortOrder": "DESCENDING"}]; 

var myJsonText = JSON.stringify( myObj );  

//// == Updating == 
retVal.put("12", myJsonText );

= Post PW via Google Sheets API v4

Method: POST (with Request Body)
Access URL: https://sheets.googleapis.com/v4/spreadsheets/<span style="color:red">#{data['●']}</span>:batchUpdate
Connection: OAuth 2.0
Request Body: application/json

== Request JSON sample ==

{"requests": [
  {"appendCells": 
    {
      "sheetId": 0,
      "rows": [
        {"values": [
          {"userEnteredValue": {"numberValue": 43210.12345}},
          {"userEnteredFormat": {"numberFormat": { "type": "DATE_TIME" } },
            "userEnteredValue": {"numberValue": 43210.12345}},
          {"userEnteredValue": {"stringValue": "example@example.com"}},
          {"userEnteredValue": {"stringValue": "qwerty"}}
        ]}
      ],
      "fields": "*"
    }
  },
  {"sortRange": 
    {
      "range": {
        "sheetId": 0,
        "startRowIndex": 0,
        "endRowIndex": 3
      },
      "sortSpecs": [
        {
          "dimensionIndex": 0,
          "sortOrder": "DESCENDING"
        }
      ]
    }
  }
]}

[Data Items list]