Row Addition to Google Sheet (TSV)

Adds the contents of a Table type data item to the end of a Google Spreadsheets and stores its communication log in the data item. If there exists no sheet, adds one automatically.

    A. Select TSV STRING DATAData selectStringRequired
    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 TSV STRING 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 TSV 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 myTsv = data.get( dataIdA ) + "";
    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( myTsv !== ""){
          var colIdArray = colIds.split(",");
          var numOfCols = colIdArray.length;
    
          var tsvRecords = myTsv.split("\n");
          var numOfRows = tsvRecords.length; // 行(TSVのレコード数)
    
          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
            var cellValues = tsvRecords[i].split("\t");
    
            for( var j = 0; j < numOfCols; j++ ){
              if( colIdArray[j].slice(-1) == "n" ){
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredValue": {"numberValue" : cellValues[parseInt( colIdArray[j] )] } }; 
              }else if( colIdArray[j].slice(-1) == "d" ){
                var dateTimeSerialNumber = new Date( cellValues[parseInt( colIdArray[j] )] )/86400000 + 25569;
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredFormat": {"numberFormat": { "type": "DATE_TIME" } },
                   "userEnteredValue": {"numberValue": dateTimeSerialNumber }}; 
              }else{
                myObj3.requests[0].appendCells.rows[i].values[j]
                = {"userEnteredValue": {"stringValue": cellValues[parseInt( colIdArray[j] )] }}; 
              }
            }
          }
    
          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’: TSV text 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