How do I get the equation for trendline in apps script?

52 views
Skip to first unread message

Taylor King

unread,
Mar 14, 2021, 1:02:41 PMMar 14
to Google Visualization API
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:

  1. Create multiple types of trendlines (linear, exponential, different types of polynomials, logarithmic, etc.)
  2. Compare the R^2 values of each trendline and use the one closest to 1 to do step 3.
  3. Extract the exact equation of the trendline and use it to project data.
  4. 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 chart2 = formulaSheet.newChart().asScatterChart()
    .addRange(formulaSheet.getRange(2,6,15,2))
    .setPosition(1,1,5,5)
    .setOption("trendlines",trendlinesopt)
    .build();

  formulaSheet.insertChart(chart2);

}

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?

Pat Ng

unread,
Jun 3, 2021, 7:03:19 PMJun 3
to Google Visualization API
For the equation of trendline - here is one answer.


I would also appreciate reference on getting R^2 .

Reply all
Reply to author
Forward
0 new messages