Google Sheets Append Cells

Google Sheets Append Cells
GoogleSheets-appendCells-20171031-addonxml.txt
(md5:2742f82d98612316cee9df79d7c1dbe9)

    • W’ : Spreadsheet ID stored in the selected Data Item at W
    • X’ : Sheet GID stored in the selected Data Item at X
    • A’ – J’: Data stored in the selected Data Item at A -J
    • In Sheet X’ of Spreadsheet specified by W’, add one row at the end of the sheet and input data to each cell (column A to J)
    • The communication logs will be stored in the data item selected at Y
    • You cannot specify Selected type (Checkbox), Table type, Discussion type in A to J
    • It outputs the Display label of Select type, the Name of User type and Organization type, the File name of File type.
    • For Date type (month-day / year-month / year) it outputs as the date element of “year / month / day” is supplemented with the value of “2000-01-01” when some of them missing
    • https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0

    V. Set OAuth2 Config Name (at [OAuth 2.0 Setting])Single-line inputRequired
    W. Select STRING/SELECT DATA for Spreadsheet ID (File ID)Data selectString(single line)orSelect(radio/select/search)Required
    X. Select STRING/SELECT/NUMERIC DATA for SheetGID (Worksheet ID)Data selectString(single line)orSelect(radio/select/search)orNumericRequired
    Y. Select STRING DATA for Access Log (update)Data selectString(multiple line)
    A. Select DATA for Column-A Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    B. Select DATA for Column-B Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    C. Select DATA for Column-C Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    D. Select DATA for Column-D Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    E. Select DATA for Column-E Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    F. Select DATA for Column-F Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    G. Select DATA for Column-G Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    H. Select DATA for Column-H Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    I. Select DATA for Column-I Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    J. Select DATA for Column-J Value of New RowData selectStringorNumericorDateorDatetimeorSelect(radio/select/search)orUserorOrganizationorFile
    -variable namevariable label
    Vconf_OAuth2Set OAuth2 Config Name (at [OAuth 2.0 Setting])
    Wconf_DataIdWSelect STRING/SELECT DATA for Spreadsheet ID (File ID)
    Xconf_DataIdXSelect STRING/SELECT/NUMERIC DATA for SheetGID (Worksheet ID)
    Yconf_DataIdYSelect STRING DATA for Access Log (update)
    Aconf_DataIdASelect DATA for Column-A Value of New Row
    Bconf_DataIdBSelect DATA for Column-B Value of New Row
    Cconf_DataIdCSelect DATA for Column-C Value of New Row
    Dconf_DataIdDSelect DATA for Column-D Value of New Row
    Econf_DataIdESelect DATA for Column-E Value of New Row
    Fconf_DataIdFSelect DATA for Column-F Value of New Row
    Gconf_DataIdGSelect DATA for Column-G Value of New Row
    Hconf_DataIdHSelect DATA for Column-H Value of New Row
    Iconf_DataIdISelect DATA for Column-I Value of New Row
    Jconf_DataIdJSelect DATA for Column-J Value of New Row
    // Google Sheets Append Cells (ver. 20171031)
    // (c) 2017, Questetra, Inc. (the MIT License)
    // by spreadsheets.batchUpdate [AppendCellsRequest] (not by spreadsheets.values.append)
    // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest
    
    // OAuth2 config sample at [OAuth 2.0 Setting]
    // - 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 oauth2 = configs.get( "conf_OAuth2" ) + "";
    var dataIdW = configs.get( "conf_DataIdW" ) + "";
    var dataIdX = configs.get( "conf_DataIdX" ) + "";
    var dataIdY = configs.get( "conf_DataIdY" ) + "";
    var dataIdA = configs.get( "conf_DataIdA" ) + "";
    var dataIdB = configs.get( "conf_DataIdB" ) + "";
    var dataIdC = configs.get( "conf_DataIdC" ) + "";
    var dataIdD = configs.get( "conf_DataIdD" ) + "";
    var dataIdE = configs.get( "conf_DataIdE" ) + "";
    var dataIdF = configs.get( "conf_DataIdF" ) + "";
    var dataIdG = configs.get( "conf_DataIdG" ) + "";
    var dataIdH = configs.get( "conf_DataIdH" ) + "";
    var dataIdI = configs.get( "conf_DataIdI" ) + "";
    var dataIdJ = configs.get( "conf_DataIdJ" ) + "";
    // convert 'java.lang.String' to 'javascript string'
    
    
    //// == Data Retrieving / ワークフローデータの参照 ==
    var spreadsheetId = "";
    if( engine.findDataDefinitionByNumber( dataIdW ).matchDataType( "SELECT_SINGLE" ) ){
        spreadsheetId = engine.findDataByNumber( dataIdW ).get(0).getValue() + "";
    }else{
        spreadsheetId = engine.findDataByNumber( dataIdW ) + "";
    }
    var sheetId       = "";
    if( engine.findDataDefinitionByNumber( dataIdX ).matchDataType( "SELECT_SINGLE" ) ){
        sheetId       = engine.findDataByNumber( dataIdX ).get(0).getValue() + "";
    }else if( engine.findDataDefinitionByNumber( dataIdX ).matchDataType( "DECIMAL" ) ){
        sheetId       = engine.findDataByNumber( dataIdX ) + "";
        sheetId       = sheetId.replace(/,/g,"").replace(/\./g,""); // Remove dots and commas
    }else{
        sheetId       = engine.findDataByNumber( dataIdX ) + "";
    }
    
    
    //// == Calculating / 演算 ==
    // preparing for API Request
    var apiRequest = httpClient.begin(); // HttpRequestWrapper
    // com.questetra.bpms.core.event.scripttask.HttpClientWrapper
    
    // Request HEADER (OAuth2 Token, HTTP Basic Auth, etc)
    var token  = httpClient.getOAuth2Token( oauth2 );
    apiRequest = apiRequest.bearer( token );
    
    // Request PATH (https://example.com/abc/def/)
    var apiUri = "https://sheets.googleapis.com/v4/spreadsheets/";
        apiUri += spreadsheetId + ":batchUpdate";
    
    // Request QUERY (?a=b)
    // (no set)
    
    // Request BODY (JSON, Form Parameters, etc)
    function cellDataObject( dataId ){
      var dataObj = {};
      dataObj.userEnteredValue = {};
      if( dataId === "" ){
        dataObj.userEnteredValue.stringValue = "";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "SELECT_SINGLE" ) ){
        dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).get(0).getDisplay() + "";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "FILE" ) ){
        dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).get(0).getName() + "";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "QUSER" ) ){
        dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).getName() + "";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "QGROUP" ) ){
        dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).getName() + "";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DATE" ) ){
        var serialDays = ( engine.findDataByNumber( dataId ).getTime() + engine.getTimeZoneOffsetInMinutes() * 60000 ) / 86400000 + 25569;
        dataObj.userEnteredValue.numberValue = serialDays;
        dataObj.userEnteredFormat = {};
        dataObj.userEnteredFormat.numberFormat = {};
        dataObj.userEnteredFormat.numberFormat.type = "DATE";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DATETIME" ) ){
        var serialDays2 = ( engine.findDataByNumber( dataId ).getTime() + engine.getTimeZoneOffsetInMinutes() * 60000 ) / 86400000 + 25569;
        dataObj.userEnteredValue.numberValue = serialDays2;
        dataObj.userEnteredFormat = {};
        dataObj.userEnteredFormat.numberFormat = {};
        dataObj.userEnteredFormat.numberFormat.type = "DATE_TIME";
      }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DECIMAL" ) ){
        dataObj.userEnteredValue.numberValue = engine.findDataByNumber( dataId ) - 0;
      }else{ // for "STRING" 
        dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ) + "";
      }
      return dataObj;
    }
    var requestObj = {};
      requestObj.requests = [];
      requestObj.requests[0] = {};
      requestObj.requests[0].appendCells = {};
      requestObj.requests[0].appendCells.sheetId = sheetId;
      requestObj.requests[0].appendCells.fields = "*";
      requestObj.requests[0].appendCells.rows = [];
      requestObj.requests[0].appendCells.rows[0] = {};
      requestObj.requests[0].appendCells.rows[0].values = [];
    
      requestObj.requests[0].appendCells.rows[0].values[0] = cellDataObject( dataIdA );
      requestObj.requests[0].appendCells.rows[0].values[1] = cellDataObject( dataIdB );
      requestObj.requests[0].appendCells.rows[0].values[2] = cellDataObject( dataIdC );
      requestObj.requests[0].appendCells.rows[0].values[3] = cellDataObject( dataIdD );
      requestObj.requests[0].appendCells.rows[0].values[4] = cellDataObject( dataIdE );
      requestObj.requests[0].appendCells.rows[0].values[5] = cellDataObject( dataIdF );
      requestObj.requests[0].appendCells.rows[0].values[6] = cellDataObject( dataIdG );
      requestObj.requests[0].appendCells.rows[0].values[7] = cellDataObject( dataIdH );
      requestObj.requests[0].appendCells.rows[0].values[8] = cellDataObject( dataIdI );
      requestObj.requests[0].appendCells.rows[0].values[9] = cellDataObject( dataIdJ );
    
    apiRequest = apiRequest.body( JSON.stringify( requestObj ), "application/json" );
    
    // Access to the API (POST, GET, PUT, etc)
    var response = apiRequest.post( apiUri ); // HttpResponseWrapper
    var httpStatus = response.getStatusCode() + "";
    var accessLog = "---POST request--- " + httpStatus + "\n";
    accessLog += response.getResponseAsString() + "\n";
    //var responseObj = JSON.parse( response.getResponseAsString() );
    
    // Retrieve Properties from Response-JSON
    // (no action)
    
    // Error Handling - https://stripe.com/docs/api#errors
    // (no action)
    
    
    //// == Data Updating / ワークフローデータへの代入 ==
    if ( dataIdY !== "" ){ engine.setDataByNumber( dataIdY, accessLog ); }