Okay, so I waited the weekend and gave it another go: using the API, sending inputValue '=COUNTIF(sheet2!H:H,A1)' still yields '=COUNTIF(sheet2!C[-11],A1)'; not good cause my formula should be calculating based on a range of cells
sending inputVaule '=COUNTIF(sheet2!C[-11]:C[-11]:A1)' yields '=COUNTIF(sheet2!H:H:A1)'; this is not the 'standard' notation, but the UI translates it into human-speak when peering into the cell's formula (there is looks 'standard'), and the resulting formula contains the range of cells that are need for proper computation. I've tested other functions with the same results. This indicates to me that I must determine the spacial relationship of the column range to the cell that's receiving the insert. That is, in my example the formula uses column H and the cell that I'm inserting the formula into belongs to column S, so there's 11 columns (to the left) difference. insert your formulas with a relational column notation "C[(SIGNED_INT)]", where (SIGN_INT) is a signed integer representing the spacial difference (negative to the left, positive to the right) from the cell that's receiving the formula. This is necessary for ranges, but may not be for individual cells*. I assume this is done for internationalization and encoding. I spent a few days scratching my head around this, is there anyway to get this into the API documentation or guidelines/reference? Please correct me if I've missed the mark here. Best Regards, -David *PS, i think the full notation for a cell would be something like R10C11 for Row 10 Column H. This is the best reference I could find in the API/Guidelines: http://code.google.com/apis/spreadsheets/reference.html#cells_Parameters On Sep 5, 1:12 pm, eathaiku <[EMAIL PROTECTED]> wrote: > Jochen, All, > > I mis-spoke/typed the function we're dealing with is COUNTIF(), and > it's part of the math functions. my apologies. > > Yes, it's working in the UI. In fact if I replace the standard column > syntax H:H in the UI with C[-11]:C[-11], the function does as > expected. That's why I'm not too concerned about the change in > syntax; we just need to figure out why it's a change from a range to a > singe cell reference > > If I use a simple function, say SUM(C:C), where sheet1!C contains some > integers, the UI is fine the sum is calculated and inserted, but the > API updateCell will yield SUM(C[1]) which is not a range but, again, a > single cell reference. > > I'm happy to trace it, thought I don't know how; never done a packet > trace. > > Many Thank Jochen, > -David > > On Sep 5, 12:40 pm, "Jochen Hartmann (Google)" > > <[EMAIL PROTECTED]> wrote: > > David, > > > I am not really able to replicate this behavior since to my knowledge, > > there is no IFCOUNT formula. I am not seeing that in the formula > > reference either: > > >http://documents.google.com/support/spreadsheets/bin/answer.py?answer... > > > Can you confirm that this is working for you in the Spreadsheet UI? > > > The next step would be to see if you can run a packet trace of the > > actual request so that we can figure out whether the XML is being sent > > and interpreted correctly. > > > Thanks > > - jochen > > > On Sep 4, 1:51 pm, eathaiku <[EMAIL PROTECTED]> wrote: > > > > Hi all, > > > I'm having difficultly populating a cell with a funciton via the php > > > zend api. Many thanks for any clarity, as I've been racking my brain > > > all day trying to make heads or tails of this unexpected behavior. > > > > short of it: '=IFCOUNT(sheet2!H:H,A1)' yeilds ''=IFCOUNT(sheet2! > > > C[-11],A1)', a single cell reference, using a different cell > > > reference. > > > > long of it: > > > I have a spreadsheet with two worksheets. each row in sheet1 has a > > > unique value in column A. In sheet2 is additional information, each > > > row has several columns of data, one column, say column H, contains > > > one of the unique keys form sheet1!A. I want to put a IFCOUNT > > > function on sheet1 in column B that will tally the occurances of A in > > > sheet2. If i manually put the function '=IFCOUNT(sheet2!H:H,A1) in to > > > sheet!B1, the result is the number of times that unique key sheet!A1 > > > appears in sheet2. > > > > However, when I try to use the api method > > > updateCell( '1','2','=IFCOUNT(sheet2!H:H,A1)',$key,$wksht), B is > > > populated with a different formula. Usually something like > > > '=IFCOUNT(sheet2!C[-11],A1)': where C[-11]is, I guess, some column > > > reference counting back (from where?). While the conversion from one > > > column definition to another is not expected, I can (begrudgingly) > > > deal with it. The PROBLEM is that I what the whole column, hence my > > > 'H:H', but the conversion/update/insert removes the range, leaving a > > > single cell reference. WHY? > > > > Do I need to change my function? Am I not providing the column range > > > properly? is there cell reference convention that I'm missing? > > > > Again, thanks in advance > > > -david --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Google Docs Data APIs" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Google-Docs-Data-APIs?hl=en -~----------~----~----~----~------~----~------~--~---
