2022-06-01 / Add-ons

PAGE UPDATED

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.
2017 (C) Questetra, Inc. (MIT License)
Configs
  • A: Set OAuth2 Config Name (at [OAuth 2.0 Setting]) *#{EL}
  • B: Select STRING/SELECT DATA for Spreadsheet ID *
  • C: Select STRING/SELECT DATA for Sheet Title *
  • D: Select STRING/SELECT DATA for Cell Coordinate (e.g. “A1”) *
  • E: Select DATA ITEM for Stored Value *
  • X: Select STRING DATA for Access Log (update)
Script
////// == 工程コンフィグの参照 / 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 );
}

Download

  • GoogleSheets-valueUpdate.xml
    • Since Rhino (deprecated) is specified as the script engine, a setting error will occur even if you install this in a workflow App
    • To use this Add-on, you need to change the script engine and modify the script accordingly
    • A modified version is in preparation

Capture

Notes

See also

%d bloggers like this: