Modifying Google Chart - Vertical Axis 'number format' reverts to "from source data" after I update my chart title by script (shows date) dynamically

user_13848403 0
Viewed 497

I'm trying to update the title of my chart through the code so it is changed dynamically. When this happens, the title changes successfully, but the number format for the vertical axis changes to dates (changes to from source data).

I'm not sure where it's getting the dates either because the source data is set to Number. Changing it to 'format by' anything else using the script has no effect. It just ignores it.

Even a macro doesn't work. When I try to do this with a macro I get the error: "Exception: Unexpected error while getting the method or property setOption on object SpreadsheetApp.EmbeddedChartBuilder."

I have this code that runs when the button is pressed. It changes the title of the chart to the value in K1 but I don't understand why it changes the vertical axis to dates as well. I just want it to remain at "none" or "decimal":

function updateTitle() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var chart = sheet.getCharts()[0];
  var title = sheet.getRange('K1').getValue();

  chart = chart.modify()

  .setOption('title', title || 'Empty')
  .setOption('vAxis.format', 'none')
  .build();

   sheet.updateChart(chart);
}

I'm thinking the formatting of the code must be incorrect, is that true? I've searched all over the Internet, and I've found tons of different ways to format modifying google charts, and have tried many different variations. I'm not getting it right and am kind of lost about what format is correct and why what I'm doing isn't working.

Here is an example of my chart: https://docs.google.com/spreadsheets/d/16vmDv5sJvle4hLXaz1ZTgkP8V2-5dlWJvdJ9eqPxc-w/edit?usp=sharing

Thank you

google-apps-script google-sheets google-visualization
2 Answers

Issue and workaround:

I have experienced the same situation with your situation. In this case, even when the following simple script is run, the number format is changed.

var sheet = SpreadsheetApp.getActive().getActiveSheet();
var chart = sheet.getCharts()[0];
sheet.updateChart(chart);

But, unfortunately, at that time, although I had looked for the method for keeping the number format for updateChart, I couldn't find it. So in this answer, I would like to propose to use Sheets API for your situation. In my experience, I had confirmed that when Sheets API is used, the title can be updated without changing the number format of the vertical axis.

When your script is modified, it becomes as follows.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function updateTitle() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var chart = sheet.getCharts()[0];
  var title = sheet.getRange('K1').getValue();
  var chartId = chart.getChartId();

  sheet.getRange("F3:H5").setNumberFormat("0");
  SpreadsheetApp.flush();

  var sheets = Sheets.Spreadsheets.get(ss.getId(), {fields: "sheets(charts)"}).sheets;
  var c = sheets.reduce((ar, s) => {
    var temp = s.charts.filter(c => c.chartId == chartId);
    if (temp.length == 1) ar = ar.concat(temp);
    return ar;
  }, []);
  if (c.length == 1) {
    var chartObj = c[0];
    delete chartObj.position;
    chartObj.spec.title = title || 'Empty';
    Sheets.Spreadsheets.batchUpdate({requests: [{updateChartSpec: chartObj}]}, ss.getId());
  }
}

Result:

When above script is used for sample chart of your sample Spreadsheet, the following result is obtained.

enter image description here

Note:

  • In this case, the number format of the cells "F3:H5" is reflected to the number format of the vertical axis. So when you want to use the format like 1.00, 2.00,,,, please remove sheet.getRange("F3:H5").setNumberFormat("0") and SpreadsheetApp.flush() from above script.

  • In the case of above script, because fields cannot be used for UpdateChartSpecRequest], by this, at first, it is required to retrieve all objects of the chart and modify the object, and then, put the modified object to the chart. This situation has already been reported to Google issue tracker as the future request. Ref

References:

user_71086530

If you change the vertical axis format in any way after the graph was first made, you must leave the vertical axis format in its virgin state and never change it.

user_177649090
Related