CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom
Tom

Reputation: 68

Create chart with disjointed data in Google Sheet

I have a sheet with 3 columns (month, count, amount) over many years.

2010-01, 10, 20
2010-02, 34, 34
...
2011-01, 30, 43
2011-02, 12, 23
...
2012-01, 45, 33
2012-02, 43, 22

I want to create charts for each month over the years. So a chart for all January, February, etc.

I can do some scripting. The google chart api (for web) offers newDataTable and setDataTable, which can work for disjointed data. For the scripting in google sheets, it seems only range is available for charts. Can I create virtual range (but that is just DataTable)?:-) Any suggestion on how I can do this in Google Sheets?

** added 2020/05/12 **

I am not sure how to do query to get the appropriate dataset:-(

So I have decided to use script to create a range that is chart-friendly.

function setMonthlyStat(sheetName) {

  var ss = SpreadsheetApp.getActive().getSheetByName(sheetName);

  var value1, value2;
  var sourceX, sourceY;
  var row = 13; // number of years
  sourceX = 2; sourceY = 2; // starting x, y

  // continuous monthly value year by year (j*12 = offset by year)
  var stats = new Array(row);
  for (var i=0; i<=row-1; i++) { // vertical
    stats[i] = new Array(24);
    for (var j=0; j<=11; j++) { // horizontal
      // count
      value1 = ss.getRange(sourceX+j+i*12, sourceY).getValue();
      stats[i][j*2] = value1;
      // amount
      value2 =  ss.getRange(sourceX+j+i*12, sourceY+1).getValue();
      stats[i][j*2+1] = value2;
    }
  }
  ss.getRange("L2:AI14").setValues(stats); // the range is not the one in the screenshot
}

Note: ncount = #, namount = $

enter image description here

Upvotes: 0

Views: 430

Answers (1)

kirkg13
kirkg13

Reputation: 3010

This may work for you. If I understand correctly, your issue is getting the data into a format that you can make your chart from, not the actual charting.

This formula I think converts your original data into the format you need. See the sample sheet below.

=query(DataA,"select D, sum(B), sum(C) " & L1 & " group by D pivot E label sum(B) 'Amt', sum(C) 'Cnt'",1)

A sample sheet is here: https://docs.google.com/spreadsheets/d/12wboxcbSu-SBhbHzZWrhmVSLMt1QoyLYu8ErUuWlPJg/edit?usp=sharing

I've added some helper columns (D:F, hidden), but these could be removed and incorporated within the formula, if you prefer. I'll see about adding the chart to the sheet later today.

Note that even though the query shows "SUM" for some columns, there is only one value per month per year, so the SUM is just for one value. But it is needed if you need to be able to show all of the months for each year - ie. select "All" from the month picker in K1. If you only ever need to see one month's data (eg. February) then the formula can be much simpler, as follows:

=QUERY(DataA,"SELECT D, B, C " & L1 & " ORDER BY D ",1)

Let me know if this is on the right track.

Upvotes: 2

Related Questions