Copying selected row to presentation

Question

My goal is to copy a row from a spreadsheet and merge the row data using markers into a document (ideally a google presentation) that saves as a PDF that I can print.

I have figured out how to have the selected row copied into an object, but I can't for the life of me figure out how to get that text copied into any kind of temporary document. I have a presentation template with markers all ready to go.

Here is some of the scripts that I've been working with.

function printRepairs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var row = sheet.getActiveSelection();

  //prints a comma delim string of a single row
  var row1 = data[1]
  Logger.log("row #1: " + row1);
  var selectedRow = SpreadsheetApp.getActiveRange().getValues();

  // creates a file with selected row --doesn't work.
  Browser.msgBox("the row you selected contains: " +selectedRow);  
  DocsList.createFile('temp_row', selectedRow);
  copyblob(selectedRow);

  var folder = DocsList.getFolder('temp');
  folder.createFile('temp_row', 'hello', 'application/vnd.google-apps.document');

  doc = DocumentApp.create("sheet_test");
  DocumentApp.openById("sheet_test");
  text.insertText("sheet_test", selectedRow);

  Browser.msgBox("the row you selected contains: " +selectedRow);
  Logger.log("the row you selected contains: " + selectedRow);  
}

score 1 · Answer 1 · edited May 23 '17 at 12:22

The operation you're performing is commonly called a "mail merge" . There are Google Docs add-ons that can do this for you - from a Google Doc go to "Add-ons > Get add-ons" and search for "merge".

To do the same from your own script, I recommend you first get familiar with the material in the Simple Mail Merge tutorial. Instead of building an email from a template in a spreadsheet, you'll create a new document from a document template.

Create a google doc template, with markers that match the column headers in your spreadsheet.

I have a presentation template with markers all ready to go.

Unfortunately, there's no support for presentations (yet). Star Issue 1573: Presentation Service for Google Apps Script. (Maybe your vote will be the one that finally gets it done!)
For each row in your spreadsheet...
1. Create a copy of the template doc. DriveApp.makeCopy(name, destination). You should use a unique name for each file, but if you don't you'll just end up with multiple files with the same name on Drive.
2. Use the fileId of the new file, and open it as a document. DocumentApp.openById(id)
3. Using a technique similar to this, this or this, search through the document for your markers, and replace with data from the spreadsheet.

Copying selected row to presentation

1 Answers1