Google Spreadsheets Cell Value Update

Updates the target cell on the target sheet of the specified Google Sheets with the value of the specified Data Item. The communication log is stored in the String type Data Item.

    A. Set OAuth2 Config Name (at [OAuth 2.0 Setting])Single-line inputRequired
    B. Select STRING/SELECT DATA for Spreadsheet IDData selectString(single line)orSelect(radio/select/search)Required
    C. Select STRING/SELECT DATA for Sheet TitleData selectString(single line)orSelect(radio/select/search)Required
    D. Select STRING/SELECT DATA for Cell Coordinate (e.g. "A1")Data selectString(single line)orSelect(radio/select/search)Required
    E. Select DATA ITEM for Stored ValueData selectStringorSelect(radio/select/search)orNumericorDateorDatetimeorUserorOrganizationRequired
    X. Select STRING DATA for Access Log (update)Data selectString(multiple line)
    -variable namevariable label
    Aconf_OAuth2Set OAuth2 Config Name (at [OAuth 2.0 Setting])
    Bconf_DataIdBSelect STRING/SELECT DATA for Spreadsheet ID
    Cconf_DataIdCSelect STRING/SELECT DATA for Sheet Title
    Dconf_DataIdDSelect STRING/SELECT DATA for Cell Coordinate (e.g. "A1")
    Econf_DataIdESelect DATA ITEM for Stored Value
    Xconf_DataIdXSelect STRING DATA for Access Log (update)
    // Google Spreadsheets Value Update via Sheets API v4 (ver. 20170828)
    // (c) 2017, Questetra, Inc. (the MIT License)
    
    ////// == 工程コンフィグの参照 / Config Retrieving ==
    var oauth2  = configs.get( "conf_OAuth2" ) + "";
    // Convert from 'java.lang.String' to 'JavaScript string' by '+""'
    var dataIdB = configs.get( "conf_DataIdB" ) + ""; // Data Identification Number (e.g. "9")
    var dataIdC = configs.get( "conf_DataIdC" ) + ""; 
    var dataIdD = configs.get( "conf_DataIdD" ) + "";
    var dataIdE = configs.get( "conf_DataIdE" ) + "";
    var dataIdX = configs.get( "conf_DataIdX" ) + "";
    
    ////// == ワークフローデータの参照 / Data Retrieving ==
    var fileId = ""; // e.g. "1PEZ5KEZc0RkUn7AbAe2lYaHXzbJ9jnZ2swIWPszRz6U"
    if( engine.findDataDefinitionByNumber( dataIdB ).matchDataType( "STRING_TEXTFIELD" ) ){
      fileId = data.get( dataIdB ) + ""; // convert to JavaScript string
    }else if( engine.findDataDefinitionByNumber( dataIdB ).matchDataType( "SELECT_SINGLE" ) ){
      fileId = data.get( dataIdB ).get(0).getValue() + "";
    }
    var sheetTitle = ""; // e.g. "Sheet1"
    if( engine.findDataDefinitionByNumber( dataIdC ).matchDataType( "STRING_TEXTFIELD" ) ){
      sheetTitle = data.get( dataIdC ) + "";
    }else if( engine.findDataDefinitionByNumber( dataIdC ).matchDataType( "SELECT_SINGLE" ) ){
      sheetTitle = data.get( dataIdC ).get(0).getValue() + "";
    }
    var cellCoordinate = ""; // e.g. "A1"
    if( engine.findDataDefinitionByNumber( dataIdD ).matchDataType( "STRING_TEXTFIELD" ) ){
      cellCoordinate = data.get( dataIdD ) + "";
    }else if( engine.findDataDefinitionByNumber( dataIdD ).matchDataType( "SELECT_SINGLE" ) ){
      cellCoordinate = data.get( dataIdD ).get(0).getValue() + "";
    }
    var updateValue = "";
    if( engine.findDataDefinitionByNumber( dataIdE ).matchDataType( "SELECT_SINGLE" ) ){
      updateValue = data.get( dataIdE ).get(0).getValue() + "";
    }else{
      updateValue = data.get( dataIdE ) + "";
    }
    
    ////// == 演算 / Calculating ==
    // 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://sheets.googleapis.com/v4/spreadsheets/";
      apiUri += fileId + "/values/" + sheetTitle + "!" + cellCoordinate;
    
    // preparing for API Request (Query parameters)
    apiRequest = apiRequest.queryParam( "valueInputOption", "USER_ENTERED" );
    
    // preparing for API Request (JSON Body)
    var requestObj = {};
      requestObj.values = [];
      requestObj.values[0] = [];
      requestObj.values[0][0] = updateValue;
    apiRequest = apiRequest.body( JSON.stringify( requestObj ), "application/json" );
    
    // PUT Request to the API
    var response = apiRequest.put( apiUri ); // HttpResponseWrapper
    var statusCode = response.getStatusCode() + "";
    var accessLog = "";
    accessLog += "---PUT request--- " + statusCode + "\n";
    accessLog += response.getResponseAsString() + "\n";
    
    // Retrieve Properties from Response-JSON
    // (no action)
    
    ////// == ワークフローデータへの代入 / Data Updating ==
    if( dataIdX !== "" ){
      retVal.put( dataIdX, accessLog );
    }
    

    • B’: Google Spreadsheet ID* stored in the Data Item selected at config B
    • C’: Sheet Title stored in the Data Item selected at config C
    • D’: The coordinates of the updating cell stored in the Data Item selected at config D
    • E’: Data with which to be updated that stored in the Data Item selected at config E
    • Cell D’ on Sheet C’ of Spreadsheet B’ will be Updated with value of E’
    • The communication log will be stored in the Data Item selected at config X
    • * https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0