0

I have a container bound Google apps script attached to a Google Sheet that uses the Slides API to pull data and replace values from the sheet to perform a "mail merge" on a select Google Slides presentation using a custom menu element.

The issue is, that when copying the sheet, the script functionality is copied, yet Google generates a new project for the script which does not have the Slides API enabled in the project API console.

My goal is to encapsulate the parameters for the "mail-merge" into the slides presentation to allow for those with no programming experience to make a copy of the sheet, update the slide template ID and destination drive folder (within the sheet) to allow for the dynamic generation of presentations. Everything is working great with the exception of having to manually navigate to enable the Slides API for the copied project. Is there a way to either enable the API for the new project on the fly or associate the copied container script to an existing project with the console APIs enabled?

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('WF Slides')
    .addItem('Make slide deck', 'makeSlideDeck')
    .addToUi();
}

function makeSlideDeck() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var metaSheet = sheet.getSheetByName('Meta');
  var replaceSheet = sheet.getSheetByName('Replace');   
  var fileName = metaSheet.getDataRange().getCell(2,3).getValue();
  var templateId = metaSheet.getDataRange().getCell(3,3).getValue();
  var driveId = metaSheet.getDataRange().getCell(4,3).getValue();    
  var file = DriveApp.getFileById(templateId).makeCopy(fileName, 
  DriveApp.getFolderById(driveId));
  var newPresentationId = file.getId(); 
  var range = replaceSheet.getDataRange();
  var numRows = range.getNumRows();    
  var replacements = [];
  var requests = [];  
  var i0 = 0;    
  for (var i = 2; i <= numRows; i++) {
    var val1 = range.getCell(i,2).getValue();
    var val2 = range.getCell(i,3).getValue();
    var val3 = range.getCell(i,4).getValue();
    replacements[i0] = [val1, val2, val3];
    i0++;
  }

  for (var i = 0; i <= replacements.length - 1; i++) {
    if(replacements[i][0] == 'text') {
      requests[i] = appendRequestText(replacements[i][1], replacements[i][2]);
    } else {
      requests[i] = appendRequestImage(replacements[i][1], replacements[i][2]);
    }
  }

  Slides.Presentations.batchUpdate({'requests': requests}, newPresentationId);

  SpreadsheetApp.getUi() 
    .alert('Your presentation has been generated.');
}

function appendRequestText(find, replace) {
  return {
    'replaceAllText': {
      'containsText': {'text': find, 'matchCase': true},
      'replaceText': replace
    }
  };
}

function appendRequestImage(find, replace) {
  return {
    'replaceAllShapesWithImage': {
      'imageUrl': replace,
      'replaceMethod': 'CENTER_INSIDE',
      'containsText': {
        'text': find,
        'matchCase': true
      }
    }
  };
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Ryan Charmley
  • 1,127
  • 15
  • 18
  • 1
    If you want to avoid people needing to navigate the api console to enable the Slides API, you should publish your script as a webapp or an add-on. Either method will still (correctly) require that users who utilize the function approve of it accessing their data (i.e. pass through OAuth flow). The webapp would need to allow users some manner of selecting which spreadsheet has the desired parameters. The add-on will have to be installed by the user into their sheet. – tehhowch Mar 01 '18 at 20:13
  • My fear of publishing as a webapp or add-on is that SpreadsheetApp.getActiveSpreadsheet() would no longer be available, which require the typical user to map the new ID for the spreadsheet upon copying. – Ryan Charmley Mar 02 '18 at 00:13
  • 1
    if installed as an add-on, `getActiveSpreadsheet` will refer to the spreadsheet in which the add-on is installed. In the webapp route, you could get the spreadsheet in question by having the user select the desired file from their Drive, e.g. give them a file picker. It would be easier for **you** to just ask them for the proper spreadsheet ID, but as you state, that's poor UX for the general user. – tehhowch Mar 02 '18 at 00:18
  • Okay perfect, will try the add-on approach. Thank you! – Ryan Charmley Mar 02 '18 at 00:50
  • 1
    You could also use the built-in [SlidesApp](https://developers.google.com/apps-script/reference/slides) service to work with Slides, instead of using the advanced service. SlidesApp is built-in, and doesn't need to be enabled on the API console. On the flip side, you'll have to write a loop to replicate `replaceAllShapesWithImage`. – Maurice Codik Mar 03 '18 at 03:05
  • Okay, thank you Maurice. That is more straightforward. Does Google have any examples of a Slides presentation automated using Sheets as a data source and mail-merge-like templates? – Ryan Charmley Mar 04 '18 at 21:15

0 Answers0