Hi,
To be more generic I have created a proxy which fetches a column (the
column shouldn't be empty) and updates the same column in an iteration.
The two exceptions [1] are thrown when updating more than 500 rows. The
proxy service is attached with this mail.
A sample spreadsheet is at [2] and a sample call is as given below:
{
"accesstoken":"ya29.RAK1F3bpXOp1TxuTg7N96CPFj5d4O1p5dyTCudfXmRTSz7UWK2OzTXmbZ2-_IBfuctb-",
"key":"1HI9u5PuNxURVapBPSbj_aI_eM3C5NU6p8ftTyHdHW-g",
"workSheetId":"1",
"startRowNum":"1",
"numRows":"500",
"RetrievecolNum":"1"
}
[1]
https://docs.google.com/document/d/1z37FzNjo2WHlHZHDyBoMR8zP1_w_WA_qJwz4O3AxEGk/edit
[2]
https://docs.google.com/spreadsheets/d/1HI9u5PuNxURVapBPSbj_aI_eM3C5NU6p8ftTyHdHW-g/edit#gid=0
--
Riyafa Abdul Hameed
Software Engineering intern, WSO2
Email: riy...@wso2.com
Website: https://riyafa.wordpress.com/ <http://riyafa.wordpress.com/>
<http://facebook.com/riyafa.ahf> <http://lk.linkedin.com/in/riyafa>
<http://twitter.com/Riyafa1>
<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse";
name="testUpdate"
transports="http https"
startOnLoad="true"
trace="disable">
<description/>
<target>
<inSequence><!-- The acceess token --><property name="accesstoken1"
expression="json-eval($.accesstoken)"
scope="default"
type="STRING"/>
<!-- The key of the spreadsheet --><property name="sheetKey1"
expression="json-eval($.key)"
scope="default"
type="STRING"/>
<!-- The id of the worksheet in the spreadsheet --><property name="sheetId1"
expression="json-eval($.workSheetId)"
scope="default"
type="STRING"/>
<!-- The starting row number --><property name="rowNum1"
expression="json-eval($.startRowNum)"
scope="default"
type="STRING"/>
<!-- The last row number in the sheet --><property name="rowss1"
expression="json-eval($.numRows)"
scope="default"
type="STRING"/>
<!-- The column number of the project --><property name="retrieveColNum1"
expression="json-eval($.RetrievecolNum)"
scope="default"
type="STRING"/>
<!-- fetch the key column --><googlespreadsheet.init>
<accessToken>{$ctx:accesstoken1}</accessToken>
</googlespreadsheet.init>
<googlespreadsheet.fetchSpecificRowsOrColumns>
<key>{$ctx:sheetKey1}</key>
<worksheetId>{$ctx:sheetId1}</worksheetId>
<minRow>{$ctx:rowNum1}</minRow>
<maxRow>{$ctx:rowss1}</maxRow>
<minCol>{$ctx:retrieveColNum1}</minCol>
<maxCol>{$ctx:retrieveColNum1}</maxCol>
</googlespreadsheet.fetchSpecificRowsOrColumns>
<log level="full"/>
<!-- Iterate through the cells in the column --><iterate xmlns:abc="http://www.w3.org/2005/Atom";
id="iterator1"
expression="//abc:entry"
description="">
<target>
<sequence><!-- Get the row number and the update cell Id --><property xmlns:gs="http://schemas.google.com/spreadsheets/2006";
name="rowNum"
expression="//gs:cell/@row"
scope="default"
type="STRING"/>
<property name="updateCellId"
expression="fn:concat('R', get-property('rowNum'), 'C',get-property('retrieveColNum1'))"
scope="default"
type="STRING"/>
<property name="cellRef"
expression="//abc:entry/abc:link[2]/@href"
scope="default"
type="STRING"/>
<script language="js">var cellRef=mc.getProperty("cellRef");
var x=cellRef.split("/");
var n=x.length-1;
cellRef=x[n];
mc.setProperty("cellVersion",cellRef)</script>
<property name="row"
expression="get-property('rowNum')"
scope="default"
type="STRING"/>
<property name="col"
expression="get-property('retrieveColNum1')"
scope="default"
type="STRING"/>
<property name="inputValue" value="Testing" scope="default" type="STRING"/>
<property name="cellId"
expression="get-property('updateCellId')"
scope="default"
type="STRING"/>
<property name="cellV"
expression="get-property('cellVersion')"
scope="default"
type="STRING"/>
<property name="key"
expression="get-property('sheetKey1')"
scope="default"
type="STRING"/>
<property name="id"
expression="get-property('sheetId1')"
scope="default"
type="STRING"/>
<property name="accesstoken"
expression="get-property('accesstoken1')"
scope="default"
type="STRING"/>
<property name="Accept-Encoding" scope="transport" action="remove"/>
<googlespreadsheet.init>
<accessToken>{$ctx:accesstoken}</accessToken>
</googlespreadsheet.init>
<googlespreadsheet.updateCells>
<key>{$ctx:key}</key>
<worksheetId>{$ctx:id}</worksheetId>
<cellId>{$ctx:cellId}</cellId>
<inputValue>{$ctx:inputValue}</inputValue>
<row>{$ctx:row}</row>
<col>{$ctx:col}</col>
<cellVersion>{$ctx:cellV}</cellVersion>
</googlespreadsheet.updateCells>
<log level="full"/>
</sequence>
</target>
</iterate>
</inSequence>
</target>
<parameter name="ApplicationXMLBuilder.allowDTD">true</parameter>
</proxy>
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
