I've been looking for an answer, but couldn't find one. I'm quite new to the platform and I have an issue with a script generating a Combo chart in Google Sheets. The script runs successfully from the Script Editor, however when I attempt to go back to the Spreadsheet itself, I get the following error. Issue persists after reloading, clearing cache, using a different browser (Chrome default). The only way I can access the file again is by removing the tabs with another script run from a separate file. When another user opens the file, the issue is still there.
Initial data is generated dynamically, therefore I can't set the range and columns to static values. It looks like this:
Initial part of the code:
function prepareData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Sorted data2023-01-04 1"); //
var dataRange = dataSheet.getDataRange();
var data = dataRange.getValues();
var headers = data[0];
var userCol = headers.indexOf("User");
var dateCol = headers.indexOf("Date");
var hourlyTimeframeCol = headers.indexOf("Hourly Timeframe");
var appIdentifiedCol = headers.indexOf("App identified");
var subjectCol = headers.indexOf("Subject");
var potentialPassiveCol = headers.indexOf("Potential passive");
var potentialActiveCol = headers.indexOf("Potential active");
var userData = {};
for (var i = 1; i < data.length; i++) {
var row = data[i];
var user = row[userCol];
var date = Utilities.formatDate(new Date(row[dateCol]),"GMT+7","yyyy-MM-dd");
var hourlyTimeframe = row[hourlyTimeframeCol];
var appIdentified = row[appIdentifiedCol];
var subject = row[subjectCol];
var potentialPassive = row[potentialPassiveCol];
var potentialActive = row[potentialActiveCol];
potentialPassive = Number(potentialPassive);
potentialActive = Number(potentialActive);
if (!userData[user]) {
userData[user] = {};
}
if (!userData[user][date]) {
userData[user][date] = {};
}
if (!userData[user][date][hourlyTimeframe]) {
userData[user][date][hourlyTimeframe] = {
"potentialPassive": 0,
"potentialActive": {},
"count": 0
};
}
if (!userData[user][date][hourlyTimeframe]["potentialActive"][appIdentified + "_" + subject]) {
userData[user][date][hourlyTimeframe]["potentialActive"][appIdentified + "_" + subject] = 0;
}
userData[user][date][hourlyTimeframe]["potentialPassive"] += potentialPassive;
if (!userData[user][date][hourlyTimeframe]["potentialActive"][appIdentified + "_" + subject]) {
userData[user][date][hourlyTimeframe]["potentialActive"][appIdentified + "_" + subject] = 0;
}
userData[user][date][hourlyTimeframe]["potentialActive"][appIdentified + "_" + subject] += potentialActive;
userData[user][date][hourlyTimeframe]["count"]++;
}
Logger.log(userData);
for (var user in userData) {
for (var date in userData[user]) {
var newSheet = ss.insertSheet(user + "_" + date);
var hourlyTimeframes = Object.keys(userData[user][date]);
hourlyTimeframes.sort(function(a, b) {
return a - b;
});
var headers = ["Hourly Timeframe", "Potential passive"];
for (var hourlyTimeframe in userData[user][date]) {
for (var appSubject in userData[user][date][hourlyTimeframe]["potentialActive"]) {
if (!headers.includes(appSubject)) {
headers.push(appSubject);
}
}
}
headers.push("Count");
var rows = [headers];
for (var hourlyTimeframe in userData[user][date]) {
var rowData = [hourlyTimeframe, userData[user][date][hourlyTimeframe]["potentialPassive"]];
for (var appSubject in userData[user][date][hourlyTimeframe]["potentialActive"]) {
var index = headers.indexOf(appSubject);
if (index != -1) {
rowData[index] = userData[user][date][hourlyTimeframe]["potentialActive"][appSubject];
}
}
rowData[headers.length-1] = userData[user][date][hourlyTimeframe]["count"];
rows.push(rowData);
}
}
rows.sort(function(a, b) { return a[0] - b[0]; });
var index = rows[0].indexOf("Passive_Passive");
if(index>-1) for (var i=0;i<rows.length;i++) rows[i].splice(index,1);
newSheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
Problematic code:
var chartRange = newSheet.getDataRange();
var chartBuilder = newSheet.newChart();
// Add the data range for the chart
chartBuilder.addRange(chartRange);
// Set the chart type
chartBuilder.setChartType(Charts.ChartType.COMBO);
// set the chart to use the first column as x-axis
chartBuilder.setOption('useFirstColumnAsDomain', true);
// Set the position of the chart
chartBuilder.setPosition(3,3,0,0);
// Set the chart title
chartBuilder.setOption('title', 'Hourly Usage Data');
// Set the chart options
chartBuilder.setOption('vAxis', {
title: 'Stacked hourly time spent'
});
chartBuilder.setOption('hAxis', {
title: 'Hours'
});
// chartBuilder.setOption('height', 400);
// chartBuilder.setOption('width', 600);
chartBuilder.setOption('backgroundColor', '#f1f8e9');
// Set the series type and series options
chartBuilder.setOption('series', [{
type: 'bars',
targetAxisIndex: 0,
// color: 'red',
data: {
sourceRange: {
sheetId: newSheet.getSheetId(),
startRowIndex: 0,
endRowIndex: chartRange.getNumRows(),
startColumnIndex: 1,
endColumnIndex: chartRange.getNumColumns()-1
}
}
},{
type: 'line',
targetAxisIndex: 1,
color: 'blue',
data: {
sourceRange: {
sheetId: newSheet.getSheetId(),
startRowIndex: 0,
endRowIndex: chartRange.getNumRows(),
startColumnIndex: chartRange.getNumColumns(),
endColumnIndex: chartRange.getNumColumns()
}
}
}
]);
// Build and insert the chart
var chart = chartBuilder.build();
newSheet.insertChart(chart);
}
}
I have enabled the chart generation with another script in the same file.
function enableChartServices() {
// Enabling Chart Services
var advancedServices = PropertiesService.getScriptProperties().getProperty('services');
if (!advancedServices) {
PropertiesService.getScriptProperties().setProperty('services', JSON.stringify(['sheets','calendar','drive','charts']));
} else {
advancedServices = JSON.parse(advancedServices);
if (advancedServices.indexOf('charts') == -1) {
advancedServices.push('charts');
PropertiesService.getScriptProperties().setProperty('services', JSON.stringify(advancedServices));
}
}
}
Anyone has any idea what might be causing this? Thank you in advance!
I have tried using a column chart and it seemed to have worked. However, I need this data to be represented in a combo chart for multiple users on a daily basis (circa 300 sheets).
Edit 1: Added data table Edit 2: Added the initial part of the code (which works fine until the graph needs to be generated)
