Loading Local Spreadsheet

[suuuehgi]

Hey,

I just wanted to try the plugin for the first time and load a .xlsx-spreadsheet from a Nextcloud folder, but I have not the slightest clue how to do that. If you could add just a single example somewhere in the documentation, it would be really appreciated!

The idea: Automatically load .xlsx file from nextcloud folder, extract a data range from a certain tab (date, value) and plot it.

Thanks a lot!

[Rello]

Hello,

does this help?
https://github.com/Rello/analytics/wiki/Datasource-Spreadsheet

[suuuehgi]

Hello,

does this help? https://github.com/Rello/analytics/wiki/Datasource-Spreadsheet

Thank you! This helped a lot! 🙏 I somehow overlooked this in the wiki. This might be a really cool program!

But after 1,5 h I'm still struggling to even create a simple graph.

Task

The task is as simple as it gets: I have a spreadsheet and want to plot two lines in a single graph: A4:A18,F4:G18.

Example Data

Time series up to 09/13/2026 in German formatting. Dates from the future have no entries:

Date / A	[...]	Electricity / F	Gas / G
13.09.2026	[...]	8.1	.7
20.09.2026	[...]

Problems

I have so many questions. I just list all the questions that come into my head and I try to sort them hierarchically.

1. It would be really nice to simply have two example pictures in the wiki explaining what a report and what a panorama is. I can only see something like that on the Analytics start page under Quickstart -> Intro for a panorama. It would be nice to at least have two example pictures in the wiki, one for a report and one for a panorama.
2. I'm struggling to create a report with a plot of two lines. More by accident I managed to plot two lines in a single plot using a real-time data report using A4:A18,F4:G18.
   • 2.1 I still have no clue how to do that using a dataset.
     • 2.1.1. In datasets -> Setting: What is the function of Column headers, Column 1, Column 2 and Value? Why are there always two Columns?
     • 2.1.2 Same for the three corresponding columns in Data. No clue what this means / represents.
     • 2.1.3 On Automation: Do I have to add a separate source under Data load for every data column? That would at least neatly sort it but I again have no clue how to choose that from a report.
   • 2.2 The dates are printed erroneously: They are printed as 13/00/26 (see below). Adding {"scales":{"x":{"time":{"parser":"DD.MM.YYYY"}}}} does not change a thing (and there is no documentation about the parser syntax. I just guessed from the example DD.MM).
     • 2.2.1 How to correctly merge chart options? It's prefilled with {"plugins":{"legend":{"display":true}}}. Is {"plugins":{"legend":{"display":true}}, "scales":{"x":{"time":{"parser":"DD.MM.YYYY"}}}} a correct merge?
   • 2.3 For one curve the missing values are replaced by zeros for the other one not (see below). Both shouldn't be replaced by zeros at all – just plot what's there – but I don't find any settings at all related to that.
   • 2.4 How can I automatically adapt the data range? Over the time the lists grows but plotted are just the rows for which there is data. I don't want to manually adapt the data range every time (e.g. A4:A6,F4:G6 -> A4:A7,F4:G7, etc.).
   • 2.5 How can I activate a second y-axis in a sensible way (not adding a second grid / the second grid should adapt to the first one or both meet somewhere in the middle)? The values of the two curves are about one order of magnitude apart. With a scaling factor of 5 or 10 that should work (5 visually separates the lines while keeping readability while 10 scales them to about the same magnitude).
   • 2.6 The variable expansion results in wrong dates: %lastUpdateDate% at %lastUpdateTime% -> 01.01.70 at 01:00 (see below).
3. On Basic settings -> Visualization -> for Chart options and Data options, both info links target to "[...]/wiki/Advanced-chart-options". Is that correct?

To keep the effort on your end to a minimum, instead of overhauling the wiki (even this post took me another 1,5 h just to write everything up), it would alternatively also be really helpful to simply have one or two YouTube videos (one for a report and one for a panorama) where you create a report/panorama, briefly explaining what the various entries mean/what they are good for.

Thanks a lot for any help!

[KitoFemboy16]

On 2.2, there is a useful distinction between parsing the incoming date and formatting the axis label. The current repository uses a Moment adapter with separate parse and format paths, so changing time.parser alone need not change the label format.[1]

I checked the bundled Moment library at commit aafc02cec3f59d5eeb305b50566ce46fb5310672 with your example date (a local library test, not a Nextcloud installation):

const d = moment('13.09.2026', 'DD.MM.YYYY', true);
d.isValid();           // true
d.format('DD/mm/YY');   // '13/00/26'
d.format('DD/MM/YY');   // '13/09/26'

Lowercase mm is minutes; uppercase MM is months. That reproduces the particular 13/00/26 symptom you reported, but does not yet prove your report uses that format.

Your combined plugins + scales object is valid JSON. To separate input parsing from display, a focused diagnostic in Chart options would be:

{
  "plugins": {"legend": {"display": true}},
  "scales": {
    "x": {
      "time": {
        "parser": "DD.MM.YYYY",
        "unit": "day",
        "displayFormats": {"day": "DD.MM.YYYY"},
        "tooltipFormat": "DD.MM.YYYY"
      }
    }
  }
}

This assumes a time-scale chart whose incoming labels really are strings like 13.09.2026; it is not a fix for numeric spreadsheet date serials or already-misconverted data. The wiki distinguishes chart/axis options from per-series Data options and exposes OCA.Analytics.chartObject for debugging.[2] If this has no effect, checking OCA.Analytics.chartObject.options.scales.x.type and .time would show whether the saved settings reached the actual axis. Please share only those settings and the Analytics version if needed—not the workbook or private readings.

This addresses the date-label/JSON questions only, not the missing-value, dataset, or update-time problems. AI-assisted source investigation for Lantern Loop Studio; no paid offer. Only the bundled-library date example was executed; the report UI has not been tested.

Sources

[1] https://github.com/Rello/analytics/blob/aafc02cec3f59d5eeb305b50566ce46fb5310672/js/3rdParty/chartjs-adapter-moment.js
[2] https://github.com/Rello/analytics/wiki/Advanced-chart-options

[suuuehgi]

If this has no effect, checking OCA.Analytics.chartObject.options.scales.x.type and .time would show whether the saved settings reached the actual axis. Please share only those settings and the Analytics version if needed—not the workbook or private readings.

Thank you very much for your answer! Unfortunately, it's not affecting the displayed times.

Analytics version is 6.8.1.

OCA.Analytics.chartObject.options.scales.x.type
"category"

OCA.Analytics.chartObject.options.scales.x.time
{
  "parser": "DD.MM.YYYY",
  "tooltipFormat": "DD.MM.YYYY",
  "unit": "day",
  "displayFormats": {
    "day": "DD.MM.YYYY"
  }
}

---

Regarding 2.3: I noticed by brute-force trying that disabling "Aggregate values" under "Column selection" -> "Visualizations" disables the zeros. I have no idea why it has the effect it has and it still smells like a bug to me that it only affects one of the quantities.

Regarding 2.5: I found that under "Chart options" -> "Visualizations" setting one of the quantity's "Vertical axis" to "Secondary" creates a second y-axis on the right. But three problems remain:

1. There is no indication which axis depicts what.
2. The scaling question remains open: In my opinion it would enhance readability to slightly separate the curves.
3. If today was a day for wishes, I would add a third y-axis for a third quantity.

So this is the current state of affairs:

[KitoFemboy16]

Thank you—the "category" result is the important new detail. My earlier example assumed a time scale, so it was incomplete for your current chart. Your time settings reached the options object, but that alone did not select a time axis.[2]

I checked the 6.8.1 tag, rather than assuming current master matches your installation: its datetime chart branch explicitly sets scales.x.type = 'time'; custom chart options are composed afterward.[1]

For a narrow, reversible diagnostic, first save your current Chart options. In the object from my previous reply, add "type": "time" directly inside "x", alongside—not inside—"time". Keep your other settings:

{
  "plugins": {"legend": {"display": true}},
  "scales": {
    "x": {
      "type": "time",
      "time": {
        "parser": "DD.MM.YYYY",
        "unit": "day",
        "displayFormats": {"day": "DD.MM.YYYY"},
        "tooltipFormat": "DD.MM.YYYY"
      }
    }
  }
}

After saving/reloading, check the same .options.scales.x.type expression again. If it becomes "time" but the dates disappear or remain wrong, restore the saved options: the next thing to inspect is the input to the chart, not more label-format settings. Locally inspect OCA.Analytics.chartObject.data.labels and the first dataset's .data to see whether x-values are still date strings like 13.09.2026, numeric serials, or already malformed strings. Please don't paste the readings or workbook; just reporting which of those shapes you see is enough.

This is a diagnostic, not a verified fix for the original date conversion. I have checked the versioned source and JSON syntax, not run your Nextcloud report. I'll keep the aggregation and y-axis questions separate rather than infer their causes from this result. AI-assisted follow-up for Lantern Loop Studio; no paid offer.

Sources

[1] https://github.com/Rello/analytics/blob/e4abb67a6b4326998cfc959688c352321afa9d31/js/visualization.js
[2] #589

[suuuehgi]

The graphs indeed disappear. The labels already seem malformed:

OCA.Analytics.chartObject.data.labels
[
  "28/00/26",
  "13/00/26",
  "01/00/26",
  [...]
]

{
  [...]
  "data": [
    {
      "y": null,
      "x": "28/00/26"
    },
    {
      "y": 6.1,
      "x": "13/00/26"
    },
    {
      "y": 6.5,
      "x": "01/00/26"
    },
    [...]
  ],
  "label": "Strom"
}

I noticed that the default display formatting in LibreOffice Calc is 28.08.26 (syntax of date +%d.%m.%y – whatever syntax is used by Analytics [DD.MM.yy?]) although the value is stored as 28.08.2026.
Since I set the cell type to "Date" I had assumed that a generic date would be passed that is independent of the local display format. However, that does not seem to be the case, as the dates in Analytics change when the local display format in Calc is altered ... 🙄
Actually I do want to keep the current %d.%m.%y style.

But changing the local display style to %d.%m.%Y effectively does not alter the symptoms: The labels and data both just change to "28/00/2026", etc.

I wonder why the .type is set to "category". I chose "Chart options" -> "Data format" -> "Timestamps" (-> "Use first column for time-series indexing."). Thus I would expect that to be "time".

[suuuehgi]

Regarding 2.5: This is how I would like it to look (mock-up data). I am still unsure which one is better from a pedagogical standpoint.

Either all curves visually apart:

or the second and third y-axis using the same limits:

But still, I have no clue how to do that in Analytics.

[KitoFemboy16]

That new output narrows this down: the month is already 00 in both the labels and dataset x-values, before a time-axis parser can help. Please restore the saved chart options for now; forcing a time scale cannot recover a month that has already been lost.[2]

There is a concrete suspect in 6.8.1's spreadsheet reader, not just the chart configuration. LocalSpreadsheet::convertExcelDate() reads the cell's number-format code and translates it with a hard-coded map containing "MM" => "m" (month) but "mm" => "i" (minutes). It then overwrites the output value using $date->format($targetFormat). The spreadsheet display format therefore does affect the imported string in this path.[1]

If the format code reaching that function is dd/mm/yy, the map yields PHP d/i/y, not d/m/y; changing only the year width would leave that month/minute problem intact. This is a source-based explanation consistent with your diagnostics, not yet a reproduction using your file. The exact stored format code and file type still matter. My earlier Moment example reproduced the appearance of the error, but did not locate its origin; this reader is now the more useful place to investigate.[1][2]

A small regression case for the maintainer would be a separate, nonprivate spreadsheet containing only a header and a synthetic date such as 28 August 2026, saved in the same file format with the same date-cell formatting. Check the reader's output before chart creation. No electricity/gas readings or original workbook are needed. I would not globally replace mm with MM: actual time-of-day formats need minute handling too.

This leaves the unexpected category axis as a separate issue; repairing that alone will not repair these incoming strings. AI-assisted source investigation for Lantern Loop Studio; no paid offer. PHP is unavailable in this environment, so I have not executed the reader or verified a Nextcloud fix.

Sources

[1] https://github.com/Rello/analytics/blob/e4abb67a6b4326998cfc959688c352321afa9d31/lib/Datasource/LocalSpreadsheet.php
[2] #589

[Rello]

@suuuehgi i will need to read though the lot of text first :-)

[KitoFemboy16]

On the separate 2.5 / secondary-axis limits question, there is a version-specific obstacle worth flagging before trying more JSON.

In 6.8.1's chartOptions.js, MANAGED_PATHS includes the entire scales.secondary object. Both the advanced-editor save path and compose() strip that object; the GUI patch then sets secondary.display from the series' yAxisID.

I executed that version's compose() in Node with fictional options: a secondary axis with min: 0, max: 10 and a title, plus a series assigned to secondary. The result kept display: true but dropped those custom limits and title. This was an isolated options-function test, not a Nextcloud/UI reproduction.

So a generic Chart.js recipe targeting scales.secondary would not survive this path. That leaves a concrete maintainer question: should secondary-axis customization be preserved while only its visibility is GUI-managed? I haven't established a supported third-axis configuration or a fix for your mock-ups. Leaving those open rather than suggesting another unverified configuration.

AI-assisted investigation for Lantern Loop Studio; no paid offer.

[suuuehgi]

A small regression case for the maintainer would be a separate, nonprivate spreadsheet containing only a header and a synthetic date such as 28 August 2026, saved in the same file format with the same date-cell formatting.

Attached is the asked spreadsheet: debug.xlsx
Interestingly, with it I don't get any graphs at all (using cell range A1:D7), although the data is present in the OCA.Analytics.chartObject.data object.

@Rello Honestly, I'm on the brink of dumping the idea of using this plugin. I've already spent so many hours on it and don't even have a single working test example – not even speaking of fancy customizations.

[KitoFemboy16]

The attachment supplies the missing format-code evidence; there is no need to change your original workbook for this investigation.

I inspected debug.xlsx as ZIP/XML, without running macros or a Nextcloud instance. Its sheet dimension is A1:D7, with a header row and six data rows. Cells A2:A7 are numeric date serials, all using the stored number format dd/mm/yy—not merely a guessed LibreOffice display setting.

That is the exact format implicated by 6.8.1's convertExcelDate(): its map translates dd/mm/yy to PHP d/i/y, treating the middle component as minutes. This strengthens the reader-level diagnosis, but does not yet establish why this sample produces no visible graph. Data being present in .data is not the same as valid time-axis input.

For maintainer reproduction, the attached file and A1:D7 now provide a concrete case: compare the reader's date strings with the stored dates before investigating chart rendering. I have not executed PHP/PhpSpreadsheet or reproduced the Nextcloud UI, so I am leaving the blank-chart cause and a fix open rather than asking you to try another speculative configuration.

AI-assisted investigation for Lantern Loop Studio; no paid offer.