Based on the sheet here, I want to be able to generate a line graph for every Breakdown in the sheet.
Using the code below, I was able to list and split the rows I want into tuples and create a DataTable but the Embedded chart requires a range.
How do I adjust the below to allow me to paste the line chart in the sheet (and then link it to slides) using a range based on the unique Breakdowns in the data.
<code>function getCols(arr,cols) {
return arr.map(row =>
row.filter((_,i) => cols.includes(++i)))
}
function createLineChartsForBreakdowns() {
var sheetId = '1o7YMSSRu3XIraPEAad0I0Q0fNUtmEKDlJt5zSJlKBgM'; // Replace with your sheet ID
var sheetName = 'testSheet'; // Replace with your sheet name
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(sheetName);
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var breakdowns = [];
var breakdownData = {};
var breakdownDataTable = {};
// Get unique breakdown values and store data for each breakdown
for (var i = 1; i < data.length; i++) {
var breakdown = data[i][0];
if (!breakdowns.includes(breakdown)) {
breakdowns.push(breakdown);
breakdownData[breakdown] = (getCols(data.filter(function (row) {
return row[0] == breakdown
}),[2,3]));
}
}
for (var k = 0; k < breakdowns.length; k++) {
Logger.log(breakdownData[breakdowns[k]])
breakdownDataTable[breakdowns[k]] = Charts.newDataTable()
.addColumn(Charts.ColumnType.NUMBER, "X")
.addColumn(Charts.ColumnType.NUMBER, "Y")
for (var l =0; l<breakdownData[breakdowns[k]].length; l++)
breakdownDataTable[breakdowns[k]]
.addRow(breakdownData[breakdowns[k]][l])
.build();
}
Logger.log(breakdown)
Logger.log(breakdownData[breakdown])
Logger.log(breakdownDataTable[breakdown])
// Create a separate line chart for every breakdown
var charts = [];
for (var i = 0; i < breakdowns.length; i++) {
var breakdown = breakdowns[i];
var title = breakdown + ' Line Chart';
var chart = sheet.newChart().asLineChart()
.setTitle(title)
.setXAxisTitle('X')
.setYAxisTitle('Y')
//.setDimensions(600, 500)
.setCurveStyle(Charts.CurveStyle.SMOOTH)
.setPointStyle(Charts.PointStyle.MEDIUM)
//.setDataTable(breakdownDataTable[breakdown])
.build();
sheet.insertChart(chart);
}
}
</code>
<code>function getCols(arr,cols) {
return arr.map(row =>
row.filter((_,i) => cols.includes(++i)))
}
function createLineChartsForBreakdowns() {
var sheetId = '1o7YMSSRu3XIraPEAad0I0Q0fNUtmEKDlJt5zSJlKBgM'; // Replace with your sheet ID
var sheetName = 'testSheet'; // Replace with your sheet name
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(sheetName);
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var breakdowns = [];
var breakdownData = {};
var breakdownDataTable = {};
// Get unique breakdown values and store data for each breakdown
for (var i = 1; i < data.length; i++) {
var breakdown = data[i][0];
if (!breakdowns.includes(breakdown)) {
breakdowns.push(breakdown);
breakdownData[breakdown] = (getCols(data.filter(function (row) {
return row[0] == breakdown
}),[2,3]));
}
}
for (var k = 0; k < breakdowns.length; k++) {
Logger.log(breakdownData[breakdowns[k]])
breakdownDataTable[breakdowns[k]] = Charts.newDataTable()
.addColumn(Charts.ColumnType.NUMBER, "X")
.addColumn(Charts.ColumnType.NUMBER, "Y")
for (var l =0; l<breakdownData[breakdowns[k]].length; l++)
breakdownDataTable[breakdowns[k]]
.addRow(breakdownData[breakdowns[k]][l])
.build();
}
Logger.log(breakdown)
Logger.log(breakdownData[breakdown])
Logger.log(breakdownDataTable[breakdown])
// Create a separate line chart for every breakdown
var charts = [];
for (var i = 0; i < breakdowns.length; i++) {
var breakdown = breakdowns[i];
var title = breakdown + ' Line Chart';
var chart = sheet.newChart().asLineChart()
.setTitle(title)
.setXAxisTitle('X')
.setYAxisTitle('Y')
//.setDimensions(600, 500)
.setCurveStyle(Charts.CurveStyle.SMOOTH)
.setPointStyle(Charts.PointStyle.MEDIUM)
//.setDataTable(breakdownDataTable[breakdown])
.build();
sheet.insertChart(chart);
}
}
</code>
function getCols(arr,cols) {
return arr.map(row =>
row.filter((_,i) => cols.includes(++i)))
}
function createLineChartsForBreakdowns() {
var sheetId = '1o7YMSSRu3XIraPEAad0I0Q0fNUtmEKDlJt5zSJlKBgM'; // Replace with your sheet ID
var sheetName = 'testSheet'; // Replace with your sheet name
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(sheetName);
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var breakdowns = [];
var breakdownData = {};
var breakdownDataTable = {};
// Get unique breakdown values and store data for each breakdown
for (var i = 1; i < data.length; i++) {
var breakdown = data[i][0];
if (!breakdowns.includes(breakdown)) {
breakdowns.push(breakdown);
breakdownData[breakdown] = (getCols(data.filter(function (row) {
return row[0] == breakdown
}),[2,3]));
}
}
for (var k = 0; k < breakdowns.length; k++) {
Logger.log(breakdownData[breakdowns[k]])
breakdownDataTable[breakdowns[k]] = Charts.newDataTable()
.addColumn(Charts.ColumnType.NUMBER, "X")
.addColumn(Charts.ColumnType.NUMBER, "Y")
for (var l =0; l<breakdownData[breakdowns[k]].length; l++)
breakdownDataTable[breakdowns[k]]
.addRow(breakdownData[breakdowns[k]][l])
.build();
}
Logger.log(breakdown)
Logger.log(breakdownData[breakdown])
Logger.log(breakdownDataTable[breakdown])
// Create a separate line chart for every breakdown
var charts = [];
for (var i = 0; i < breakdowns.length; i++) {
var breakdown = breakdowns[i];
var title = breakdown + ' Line Chart';
var chart = sheet.newChart().asLineChart()
.setTitle(title)
.setXAxisTitle('X')
.setYAxisTitle('Y')
//.setDimensions(600, 500)
.setCurveStyle(Charts.CurveStyle.SMOOTH)
.setPointStyle(Charts.PointStyle.MEDIUM)
//.setDataTable(breakdownDataTable[breakdown])
.build();
sheet.insertChart(chart);
}
}
Thanks you!