To create a chart or graph in a Google Sheet using Apps Script, you can use the newChart() and setPosition() methods of the Sheet class. Here’s an example code snippet:
function createChart() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var dataRange = sheet.getRange('A1:B5'); // Replace with the range containing your data var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(dataRange) .setPosition(5, 1, 0, 0) // Replace with the desired position of the chart .build(); sheet.insertChart(chart); }
In this code, the createChart function creates a column chart in the Google Sheet. The active spreadsheet is obtained using SpreadsheetApp.getActiveSpreadsheet(). You can modify this line to access a specific spreadsheet if needed.
Next, the active sheet is obtained using getActiveSheet().
You can define the range of cells containing your data using the getRange() method. In this example, the range is set to 'A1:B5', but you should change it to your desired range.
The newChart() method is called on the sheet object to create a new chart.
The setChartType() method is called on the chart object to specify the type of chart. In this example, it is set to Charts.ChartType.COLUMN to create a column chart. You can explore other chart types available in the Charts.ChartType enum for different chart options.
The addRange() method is called on the chart object, passing the dataRange as an argument. This method specifies the range of cells containing the data to be used in the chart.
The setPosition() method is called on the chart object to set the position of the chart within the sheet. In this example, the chart is positioned at row 5 and column 1. You can modify the parameters to position the chart at your desired location.
The build() method is called on the chart object to build the chart.
Finally, the insertChart() method is called on the sheet object, passing the chart object as an argument. This method inserts the chart into the sheet.
You can call the createChart function from another function or set up triggers to execute it based on your needs. When executed, it will create and insert the chart into the Google Sheet.
Feel free to modify the code to suit your specific requirements, such as creating different types of charts, customizing chart options, using different data ranges, or performing additional operations before or after creating the chart.