Hi, I looked into this as well and it just seems that the API uses a different way to represent the formulas which should have no effect on the output though. As you figured out in your last email, C[-11] just refers to column 11 which is the same thing as H:H. Can you just confirm for me whether this is actually causing problems with your ouput because it shouldn't.
I will update the documentation. Thanks for pointing this out. Cheers, - Jochen On Sep 8, 8:26 am, eathaiku <[EMAIL PROTECTED]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
