I am trying to create a dashboard that has these features: - Has one or more sheets with each sheet having a table of related data. The data format is manually defined and may be different for each sheet. - Each of the sheets has many derived columns with formulas. - There are one or more sheets with multiple charts that are hand created. It is important that the charts include data from *all* the rows for whichever the columns that are selected. - New rows are automatically added as the data is available using the gdata API.
One use case for this is that performance results for a testcase that is run daily in different versions of the product are automatically entered into the dashboard, and the charts are updated to reflected this. Some of the charts would compare the performance of current release vs the new release, and the derived columns (formula's) will show the difference in metrics between releases. E.g., a simple table could be like this: Date Old Throughput New Throughput Diff 11/07/2011 1000 1100 =C2-B2 I am trying to achieve least amount of maintenance, but I am constantly hitting roadblocks with different choices I take. I am able to map the column tags to the data and add new rows with no problems, however I don't want to have to do the same with formulas. Formulas are part of the table definition and the tool shouldn't care what those are, how many of those exist and whether they even exist. A human entering the data manually, has the luxury of extending the formulas to new rows by simply dragging at the bottom-right corner, but I can't do the same using the API. As a workaround, I found that wrapping formulas using ARRAYFORMULA() would allow me to define it once in the first row and get it reflected for all rows automatically, something like this: Date Old Throughput New Throughput Diff 11/07/2011 1000 1100 =ARRAYFORMULA(C2:C-B2:B) When new rows are added, the formula column will get automatically extended. However, this has the side effect of extending it to all the existing empty rows also, making them look non-blank to the API, so new rows don't get added to the last non-blank row, but a new row always gets added at the end, no matter how many blank rows already exist. As a workaround, I removed all the blank rows from the end, as they don't matter as long as the tool can add rows. This worked fine, until I realized that I can no longer get these new rows automatically included in the charts. The problem is that charts can't have ranges such as "C2:C", as they get automaically expanded to reflect the current number of rows, such as "C2:C50" (given, there are 50 rows at the time the chart is created). In my prior solution, I was able to add enough blank rows that I would need in the life of the dashboard in advance, and use the full ranges in the charts. But with ARRAYFORMULA()'s, I can't have blank rows, and charts can't refer to cells that are non existent at the time of their creation (so "C2:C200" is invalid if there are only 2 rows). Can anyone think of ways to achieve my goals listed at the beginning with google spreadsheets? Is there a way to workaround the issues that I list? If anyone tried to build a spreadsheet for this or a similar purpose and, could you share the techniques you employed? Thanks a lot in advance for any help with this.
