Row Addition to Google Sheet (Table)

Adds values in a Table type data item to the bottom line of a specified Spreadsheet and stores its communication log in the data item.

    A. Select TABLE DATAData selectTableRequired
    B. Set THROW COL IDs (e.g. "0,1,5d,3n,6")Single-line inputRequired
    C. Set OAuth2 Config Name (at [OAuth 2.0 Setting])Single-line inputRequired
    D. Select STRING/SELECT for Spreadsheet ID (Non-existent Error)Data selectString(single line)orSelect(radio/select/search)Required
    E. Select STRING/SELECT for Sheet Title (Non-existent Create)Data selectString(single line)orSelect(radio/select/search)Required
    F. Select STRING DATA for Access Log (update)Data selectString(multiple line)Required
    -variable namevariable label
    Aconf_DataIdASelect TABLE DATA
    Bconf_ColIdsSet THROW COL IDs (e.g. "0,1,5d,3n,6")
    Cconf_OAuth2Set OAuth2 Config Name (at [OAuth 2.0 Setting])
    Dconf_DataIdDSelect STRING/SELECT for Spreadsheet ID (Non-existent Error)
    Econf_DataIdESelect STRING/SELECT for Sheet Title (Non-existent Create)
    Fconf_DataIdFSelect STRING DATA for Access Log (update)
    // Append Table Records to Google Sheets (ver. 20160915)
    // (c) 2016, Questetra, Inc. (the MIT License)
    
    // OAuth2 config
    // 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: (Get by Google Developers Console)
    // Consumer Secret: (Get by Google Developers Console)
    
    //// == 自動工程コンフィグの参照 / Config Retrieving ==
    var dataIdA = configs.get( "conf_DataIdA" ); // (returns Number)
    var colIds = configs.get( "conf_ColIds" ) + "";
    var oauth2 = configs.get( "conf_OAuth2" );
    var dataIdD = configs.get( "conf_DataIdD" ); // (returns Number)
    var dataIdE = configs.get( "conf_DataIdE" ); // (returns Number)
    var dataIdF = configs.get( "conf_DataIdF" ); // (returns Number)
    
    //// == ワークフローデータの参照 / Data Retrieving ==
    var myTable = data.get( dataIdA );
    // myTable: com.questetra.bpms.core.model.formdata.ListArray
    var docId = "";
    if( data.get( dataIdD ) instanceof java.util.ArrayList ){
      docId = data.get( dataIdD ).get(0).getValue() + "";
    }else if( data.get( dataIdD ) instanceof java.lang.String ){
      docId = data.get( dataIdD ) + "";
    }
    var sheet = "";
    if( data.get( dataIdE ) instanceof java.util.ArrayList ){
      sheet = data.get( dataIdE ).get(0).getValue() + "";
    }else if( data.get( dataIdE ) instanceof java.lang.String ){
      sheet = data.get( dataIdE ) + "";
    }
    
    //// == 演算 / Calculating ==
    var accessLog = "";
    var responseJson = "";
    var sheetId = -1;
    var token = httpClient.getOAuth2Token( oauth2 );
    
    try{
      // check spreadsheet
      var uri = "https://sheets.googleapis.com/v4/spreadsheets/";
      uri += docId;
      uri += "?includeGridData=false";
      var response = httpClient.begin()
        .bearer(token)
        .get( uri ); 
      accessLog += "---GET request--- " + response.getStatusCode() + "\n";
      responseJson = response.getResponseAsString();
      if( response.getStatusCode() == 200 ){
        var jsonObj = JSON.parse( responseJson );
        accessLog += " SPREADSHEET TITLE: \"" + jsonObj.properties.title + "\" has";
        accessLog += jsonObj.sheets.length + " sheets\n";
        for( var i = 0; i < jsonObj.sheets.length; i++ ){
          if( jsonObj.sheets[i].properties.title == sheet ){
            sheetId = parseInt( jsonObj.sheets[i].properties.sheetId, 10 );
            accessLog += " SHEET TITLE: \"" + sheet + "\" exists (id: " + sheetId + ")\n";
          }else{
            accessLog += " SHEET TITLE: \"" + sheet + "\" does not exist\n";
          }
        }
      }else{
        sheetId = -2;
      }
    
      // add sheet (not exsist)
      if( sheetId == -1 ){  
        var uri2 = "https://sheets.googleapis.com/v4/spreadsheets/";
        uri2 += docId;
        uri2 += ":batchUpdate";
    
        sheetId = processInstance.getProcessInstanceId() - 0;
        var myObj2 = {};
        myObj2.requests = []; // Array
        myObj2.requests[0] = {};
        myObj2.requests[0].addSheet = {};
        myObj2.requests[0].addSheet.properties = {};
        myObj2.requests[0].addSheet.properties.sheetId = sheetId;
        myObj2.requests[0].addSheet.properties.title = sheet;
    
        var response2 = httpClient.begin()
          .bearer(token)
          .body( JSON.stringify( myObj2 ), "application/json" )
          .post( uri2 ); 
        accessLog += "---POST request--- " + response2.getStatusCode() + "\n";
        if( response2.getStatusCode() == 200 ){
          accessLog += " SHEET TITLE: \"" + sheet + "\" created (id: " + sheetId + ")\n";
        }
      }
    
      // append records
      if( sheetId >= 0 ){
        if( myTable !== null){
          var colIdArray = colIds.split(",");
          var numOfCols = colIdArray.length;
          var numOfRows = myTable.size() - 0; // 行(Tableの高さ)
    
          var myObj3 = {};
          myObj3.requests = []; // Array
          myObj3.requests[0] = {};
          myObj3.requests[0].appendCells = {};
          myObj3.requests[0].appendCells.rows = []; // Array
          myObj3.requests[0].appendCells.sheetId = sheetId;
          myObj3.requests[0].appendCells.fields = "*";
    
          for( var i = 0; i < numOfRows; i++ ){
            myObj3.requests[0].appendCells.rows[i] = {};
            myObj3.requests[0].appendCells.rows[i].values = []; // Array
            for( var j = 0; j < numOfCols; j++ ){
              if( colIdArray[j].slice(-1) == "n" ){
                var cellValue = myTable.get(i, parseInt( colIdArray[j] ) ) - 0;
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredValue": {"numberValue" : cellValue } }; 
              }else if( colIdArray[j].slice(-1) == "d" ){
                var dateTimeSerialNumber = new Date( myTable.get(i, parseInt( colIdArray[j] ) ) )/86400000 + 25569;
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredFormat": {"numberFormat": { "type": "DATE_TIME" } },
                   "userEnteredValue": {"numberValue": dateTimeSerialNumber }}; 
              }else{
                var cellValue = myTable.get(i, parseInt( colIdArray[j] ) ) + "";
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredValue": {"stringValue": cellValue }}; 
              }
            }
          }
    
          var uri3 = "https://sheets.googleapis.com/v4/spreadsheets/";
          uri3 += docId;
          uri3 += ":batchUpdate";
    
          var response3 = httpClient.begin()
            .bearer(token)
            .body( JSON.stringify( myObj3 ), "application/json" )
            .post( uri3 ); 
          accessLog += "---POST request--- " + response3.getStatusCode() + "\n";
          if( response3.getStatusCode() == 200 ){
            accessLog += " ADDED DATA: " + colIds + " x " + numOfRows + "cells\n";
          }
        }
      }
    }catch(e){
      accessLog += e.message + "\n";
    }
    
    //// == ワークフローデータへの代入 / Data Updating ==
    retVal.put( dataIdF, accessLog );
    

    • A’: Table stored in the Data Item selected at config A
    • D’: Google Spreadsheet ID ** stored in the Data Item selected at config D
    • E’: Sheet Title stored in the Data Item selected at config E
    • Table A’ is automatically appended on the bottom line of ‘Sheet E’ (sheet will be added if it does not exist) of Spreadsheet D’.
    • The communication logs are stored in the data item selected at F.
    • “0,1,5d, 3n, 6” means that appending ID: 0 as a String, ID: 1 as a String, ID: 5 as Date-time, ID: 3 as a number, ID: 6 as a String.
    • ** https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0