CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docsgoogle-forms
AlterJuan
AlterJuan

Reputation: 23

How to auto refresh Google Sheets graphs inside an auto generated Google Doc, every time a form is submitted

I created a Google form and a spreadsheet that analyzes the answer and make an evaluation of the scores, then creates Radar graphs that visualize the insights.

These graphs are then copied into a Google Doc template with a link enabled then later converted into a PDF and emailed to the address defined in the form.

I found on Google references to a code for auto updating the graphs inside a Slides but I cannot make it work inside the code in the submit form responses spreadsheet. I used the Google script for this task.

I need to be able to auto update, for the purpose that all that need to be automated since the submit button in the form is the only interaction with the spreadsheet, code and template doc.

The code that I found, for Google Slides

var requests = [{
  refreshSheetsChart: {
    objectId: presentationChartId
  }
}];

// Execute the request.
var batchUpdateResponse = Slides.Presentations.batchUpdate({
  requests: requests
}, presentationId);
console.log('Refreshed a linked Sheets chart with ID: %s', presentationChartId);

Upvotes: 1

Views: 339

Answers (1)

idfurw
idfurw

Reputation: 5852

  1. Open the script editor of the Google Form
  2. Paste the following code and save (fill the presentationId and presentationChartId)
  3. Enable Slides API (Resources -> Advanced Google Services -> Google Slides API -> ON)
  4. Create Trigger (Select event type: On form submit)
function onSubmit() {
  const presentationId = '';
  const presentationChartId = '';
  var requests = [{
    refreshSheetsChart: {
      objectId: presentationChartId
    }
  }];
  
  // Execute the request.
  var batchUpdateResponse = Slides.Presentations.batchUpdate({
    requests: requests
  }, presentationId);
  console.log('Refreshed a linked Sheets chart with ID: %s', presentationChartId);
}

Upvotes: 3

Related Questions