CODEWITHSUNDEEP
google-apps-script
wivku
wivku

Reputation: 2703

correctly insert chart from Google Sheet into Document using Google Apps Script

I want to copy charts from my spreadsheet to a document using Google apps script. Inserting the charts works, but there is an issue with a) permissions and b) formatting.

Charts can be inserted as follows:

var b = d.getBody();
var charts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("charts").getCharts();
for (var i in charts) {
  b.appendImage(charts[i]);
  Logger.log(charts[i].getBlob().getName()); // correct: "chart.png"
}

Top row is manually copied, bottom row is the result of the script. manual and script

The second -related?- issue: the spreadsheet needs to be shared ("anyone with the link can view"), otherwise it shows the image (correct name, width, height, etc.) as an error message: User not signed-in. Sign in.

Any suggestions how to insert the chart with the correct formatting and without having to share the spreadsheet?

Upvotes: 5

Views: 6174

Answers (3)

Dave
Dave

Reputation: 21

For what its worth I just used the original script in a simple report builder that takes data and charts from a Google Sheet and auto-generates a report containing them for any user. It worked very easily. Was a slightly modified version of the following

var charts = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("charts").getCharts();
for (var i in charts) {
  doc.getBody.appendImage(charts[i]);
  Logger.log(charts[i].getBlob().getName())};

where in my script "doc" is a var that repr

Upvotes: 2

wivku
wivku

Reputation: 2703

Stefan pointed me in the right direction (thanks!). It appears the rendering is done quite differently when pasting/saving images. Different axis settings, colors, theme, etc.

I ended up manually creating the chart. E.g. for the first bar chart, the following results in a chart that appears the same in the spreadsheet and in the document:

var chart = dest.newChart();
chart
  .setChartType(Charts.ChartType.BAR)
  .addRange(myrange)  
  .setPosition(2,8,0,0)
  .setOption("theme","maximized")
  .setOption("colors",["#3366CC","#FF9900","#DC3912","#109618"])
  .setOption('isStacked', true)
  .setOption('width', 500)
  .setOption('height', 130)
  .setOption('hAxis.viewWindow.max', countActions)
  .setOption('vAxis.gridlines.count', 0)
  .setOption('legend', {position: 'in', textStyle: {fontSize: 12}})

var chart = chart.build();
dest.insertChart(chart);

I also had to change the data range, as it appears the following chart settings can not be set manually.

  • switch rows/columns
  • use first row as header

To solve that I have manually transposed the range in the spreadsheet (=TRANSPOSE(original_range)) and let myrange point to that new range, and I have added an empty column so that one is used as an empty header.

I have not looked at the line chart yet, it could be the Chart API can not create a timeline in the same way as the Spreadsheet does.

Upvotes: 5

Stefan van Aalst
Stefan van Aalst

Reputation: 778

Have a look at: Reading title of Graph

When I was playing around, I remember having issues as well like you described. When I made sure all the required fields were added in the graph (so no skipping of data elements), it worked fine.

Strangly, skipping data elements didn't seem to cause issues in the graph (neither with manually copying & pasting).

Upvotes: 0

Related Questions