Javascript - How can I plot line chart for two specific rows of a table in google sheet, by writing script?

SanazShaker
October 22, 2024
116 views
0 votes
2 Answers

I have a table including some parameters that their values will be completed in 23 days.
I need a line chart for the values of row3(Comp Temp) and row6(Air Meas).

My problem is that everything is good but if I insert values for row4, the chart of row 3 will messed up and if I insert values for row 7, it will happen to chart of row6!!

I tried this script at the Extensions of Google sheet:

function createCompTempLineChart(sheet) {
  // Define the range for Comp Temp (Row 3), starting at Column B (Column 2)
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn(); // Find the last column with data

  // Set empty or zero values in other rows to prevent chart from considering them
  for (var col = startColumn; col <= lastColumn; col++) {
    sheet.getRange(4, col).setValue(''); // Empty Row 4
    sheet.getRange(5, col).setValue(''); // Empty Row 5
    // You can set additional rows to '' or 0 if needed to ensure no data influences the chart
  }

  // Explicitly restrict the range to Row 3 only (ignore other rows)
  var compTempRange = sheet.getRange(3, startColumn, 1, lastColumn - startColumn + 1); // Only Row 3

  // Create or update the Comp Temp chart
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // Set chart type to Line Chart
    .addRange(compTempRange) // Only use Row 3 data
    .setTransposeRowsAndColumns(true) // Treat Row 3 as series
    .setPosition(30, 5, 0, 0) // Adjust position for the chart
    .setOption('title', 'Comp Temp (Row 3)') // Title for the Comp Temp chart
    .build();

  sheet.insertChart(chart); // Insert the Comp Temp chart
}

function createAirMeasLineChart(sheet) {
  // Define the range for Air Meas (Row 6), starting at Column B (Column 2)
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn(); // Find the last column with data

  // Set empty or zero values in other rows to prevent chart from considering them
  for (var col = startColumn; col <= lastColumn; col++) {
    sheet.getRange(3, col).setValue(''); // Empty Row 3 to prevent influence in this chart
    sheet.getRange(4, col).setValue(''); // Empty Row 4
    sheet.getRange(5, col).setValue(''); // Empty Row 5
    // You can set additional rows to '' or 0 if needed
  }

  // Explicitly restrict the range to Row 6 only (ignore other rows)
  var airMeasRange = sheet.getRange(6, startColumn, 1, lastColumn - startColumn + 1); // Only Row 6

  // Create or update the Air Meas chart
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // Set chart type to Line Chart
    .addRange(airMeasRange) // Only use Row 6 data
    .setTransposeRowsAndColumns(true) // Treat Row 6 as series
    .setPosition(50, 5, 0, 0) // Adjust position for the chart
    .setOption('title', 'Air Meas (Row 6)') // Title for the Air Meas chart
    .build();

  sheet.insertChart(chart); // Insert the Air Meas chart
}

Answers

Your problem is chart picks up other rows when you add data. Instead of emptying other rows, just set chart range to only the row you want.

Try this:

For row 3 chart:

function createCompTempLineChart(sheet) {
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn();
  var compTempRange = sheet.getRange(3, startColumn, 1, lastColumn - startColumn + 1); // Only Row 3

  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(compTempRange)
    .setPosition(5, 5, 0, 0)
    .setOption('title', 'Comp Temp (Row 3)')
    .build();

  sheet.insertChart(chart);
}

For row 6 chart:

function createAirMeasLineChart(sheet) {
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn();
  var airMeasRange = sheet.getRange(6, startColumn, 1, lastColumn - startColumn + 1); // Only Row 6

  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(airMeasRange)
    .setPosition(20, 5, 0, 0)
    .setOption('title', 'Air Meas (Row 6)')
    .build();

  sheet.insertChart(chart);
}

Don’t use setTransposeRowsAndColumns(true) unless needed. This way, adding data to other rows won’t mess up your charts.

Google Sheets and Charts are optimised for the use of columns as data ranges, not rows. One consequence is that whenever new rows of data are added immediately after existing ones used in a chart, the chart automatically updates to include them. This behaviour cannot be changed¹.

So one option is to switch from using rows to columns, by transposing your data in the spreadsheet. In this scenario, adding data to adjacent columns does not modify existing charts. Your original code would work, merely removing the setTransposeRowsAndColumns(true) line.

Another option, if you wish to keep using rows, is to include an onEdit function in your script that triggers whenever a sheet is edited and resets the embedded charts’ range – at this point, they have already updated – to only the one original row.

function createCompTempLineChart(sheet) {
  // Define the range for Comp Temp (Row 3), starting at Column B (Column 2)
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn(); // Find the last column with data

  // Explicitly restrict the range to Row 3 only (ignore other rows)
  var compTempRange = sheet.getRange(3, startColumn, 1, lastColumn - startColumn + 1); // Only Row 3

  // Create or update the Comp Temp chart
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // Set chart type to Line Chart
    .addRange(compTempRange) // Only use Row 3 data
    .setTransposeRowsAndColumns(true) // Treat Row 3 as series
    .setPosition(30, 5, 0, 0) // Adjust position for the chart
    .setOption('title', 'Comp Temp (Row 3)') // Title for the Comp Temp chart
    .build();

  sheet.insertChart(chart); // Insert the Comp Temp chart
}

function createAirMeasLineChart(sheet) {
  // Define the range for Air Meas (Row 6), starting at Column B (Column 2)
  var startColumn = 2; // Column B
  var lastColumn = sheet.getLastColumn(); // Find the last column with data

  // Explicitly restrict the range to Row 6 only (ignore other rows)
  var airMeasRange = sheet.getRange(6, startColumn, 1, lastColumn - startColumn + 1); // Only Row 6

  // Create or update the Air Meas chart
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // Set chart type to Line Chart
    .addRange(airMeasRange) // Only use Row 6 data
    .setTransposeRowsAndColumns(true) // Treat Row 6 as series
    .setPosition(50, 5, 0, 0) // Adjust position for the chart
    .setOption('title', 'Air Meas (Row 6)') // Title for the Air Meas chart
    .build();

  sheet.insertChart(chart); // Insert the Air Meas chart
}

function onEdit(e) {
  const sheet = e.range.getSheet();
  for (let chart of sheet.getCharts()) {
    const updatedRange = chart.getRanges()[0];
    const originalRange = sheet.getRange(
      updatedRange.getRow(),
      updatedRange.getColumn(),
      1,
      updatedRange.getNumColumns());
    chart = chart.modify().clearRanges().addRange(originalRange).build();
    sheet.updateChart(chart);
  }
}

Note that you may need to accept permissions the first time you run onEdit; and that you may need to create an installable trigger if your script is not bound to a spreadsheet.

¹ Based on existing options in the documentation and on this answer.