Google Sheet Objects

[Shilpa Purushothaman]

Hi,

I am working with spreadsheet object. I have a sample code which is shown below.

00130 LET PATH$="PVX_test"
00140 IF X'OPENSPREADSHEETBYPATH(PATH$)=0 THEN MSGBOX "Couldn't find "+PATH$
00200 LET S4$=" XXX"
00201 CALL "XOPENS4"
00202 GET_XXX:
00203 READ (V[1],END=GET_XXX)R1$(1)
00204 LET I=1
00211 LET R1$=SUB(R1$,"'","`"),R1$=SUB(R1$,",","~")
00212 LET A$=STP(R1::AAA$,2)
00213 LET B$=STP(R1::BBB$,2)
00214 LET C$=STP(R1::CCC$,2)
00218 LET SEP$=SEP
00220 LET CELL_VALUE$=A$+SEP+B$+SEP+C$
00230 PRINT CELL_VALUE$
00231 IF X'SETRANGECOLUMN("A:C")=0 THEN MSGBOX "Failed setting a column range"
00232 ! IF X'SETRANGEROW("A1:C100")=0 THEN MSGBOX "FAILED SETTING A ROW"
00240 IF X'WRITE(CELL_VALUE$,"A1:C100")=0 THEN MSGBOX "COULDN'T WRITE"
00290 LET I=I+1
00300 GOTO GET_XXX
00310 END_FILE:
00320 END

In this code I have created a google sheet and want to write values. How to write values in a row once that row is completed how does it go to the next row for writing the rest of the values.

Thank you

[Devon Austen]

In your example code you set a active range but then pass in a range (A1:C100) to the write command. In this case the active range will not be used and it will just do the write to the cell range passed into the write method. The write will write to cells A1 to A100, B1 to B100, and C1 to C100. Since your range covers 300 cells your cell_value$ should contain 300 SEP delimited fields. If it contains less than 300 then it will write all the fields given in order and write null to the remaining fields. i.e. If cell_value$ has 140 fields then the first 100 fields get written to cells A1 to A100 the last 40 get written to B1 to B40. B41 to B100 and C1 to C100 get blanks written to it.

Your logic has to keep track of where you want to write to in the spreadsheet. You tell it where to write by either specifying a active range and then doing a write with just cell_value$ or you just pass in the range on each write. If you want to start writing to a new column or row you just change the active range or passed in range to the write call.

DIM data$[1:100]
GOSUB READ_DATA
FOR I=1 TO 10
mySpreadsheet'SetRangeCol(I)
mySpreadsheet'Write(data$)
NEXT

Assuming data$[] has SEP delimited data for 100 rows this will write out 100 rows of data for columns A through J.

[Shilpa Purushothaman]

Thank you