Correct, if the C[-11]:C[-11] notation is sent, the proper output is rendered in the UI "H:H" (if inserting the formula in a cell belonging to column "S"). Keep in mind "-11" is a relational number. If I were to in change the column of the cell that's receiving the formula, then the "-11" would change. "-11" represents 11 columns to the left, NOT the 11th column of the spreadsheet; the 11th column would be C11 (or "K" in English).
Thanks for looking into the issue and updating the documentation Jochen, Best Regards, -David On Sep 9, 9:54 am, "Jochen Hartmann (Google)" <[EMAIL PROTECTED]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
