I am trying to use apps script to automatically build charts from a google spreadsheet, but also project what may happen in the future using trendlines. I have figured out how to create the chart with a trendline equation in the title, but I need to do a few more steps:
Create multiple types of trendlines (linear, exponential, different types of polynomials, logarithmic, etc.)
Compare the R^2 values of each trendline and use the one closest to 1 to do step 3.
Extract the exact equation of the trendline and use it to project data.
Place data in spreadsheet
Here is what I have so far:
function createChart() {
var trendlinesopt = {
0: {
type: 'linear',
color: 'black',
lineWidth: 1,
opacity: 0.2,
showR2: true,
visibleInLegend: true,
}
};
var chart = formulaSheet.newChart().asScatterChart()
.addRange(formulaSheet.getRange(2,6,15,2))
.setPosition(1,1,5,5)
.setOption("trendlines",trendlinesopt)
.build();
formulaSheet.insertChart(chart);
}
I can generate multiple charts easily, this is just consolidated to the linear function. How do I extract the R^2 value and the equation of the trendline?