Google Sheets – Generate a Formatted Document from Spreadsheet

google docsgoogle sheetsgoogle-apps-script

As the title says, I have data (Q & A grouped by subject) that I have to export to a document file.

As shown above, the source data on the left should be formatted like the example on the right

I'm looking for a free solution. Google helped me find this which looks deprecated according to comments. So I'll be very glad if at least you put me on the right path to realise this task given that I have some basic JS experience (but I've never scripted in GS).

Edit.1:

I started indeed learning how to script with Google Apps since it's a time saver as long as I'm using Google for almost everything.

At the end I'll post the full code here to help any beginner else like me.

For now I'm stuck at these points:

I'm adding a title, then I'm trying to customize it's style (font, color, alignment), but it doesn't work as expected (not everything). Plus, it looks like that order matters :-/

The following snippet gives the output in the picture:

// Define a style
var titleStyle1 = {};
titleStyle1[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.COMIC_SANS_MS;
titleStyle1[DocumentApp.Attribute.FOREGROUND_COLOR] = '#ff0000'; // Red
titleStyle1[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
titleStyle1[DocumentApp.Attribute.HEADING] = DocumentApp.ParagraphHeading.TITLE;


var titleStyle2 = {};
titleStyle2[DocumentApp.Attribute.HEADING] = DocumentApp.ParagraphHeading.TITLE;
titleStyle2[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.COMIC_SANS_MS;
titleStyle2[DocumentApp.Attribute.FOREGROUND_COLOR] = '#ff0000'; // Red
titleStyle2[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;


var titleStyle3 = {};
titleStyle3[DocumentApp.Attribute.HEADING] = DocumentApp.ParagraphHeading.TITLE;
titleStyle3[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
titleStyle3[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.COMIC_SANS_MS;
titleStyle3[DocumentApp.Attribute.FOREGROUND_COLOR] = '#ff0000'; // Red


var titleStyle4 = {};
titleStyle4[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.COMIC_SANS_MS;
titleStyle4[DocumentApp.Attribute.FOREGROUND_COLOR] = '#ff0000'; // Red
titleStyle4[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
//titleStyle4[DocumentApp.Attribute.HEADING] = DocumentApp.ParagraphHeading.TITLE;

// Create and open a new document.
var doc = DocumentApp.create(outputDocDesiredName);

var body = doc.getBody();
body.setAttributes(docStyle)

// Append a document header paragraph.
body.appendParagraph('Test title').setAttributes(titleStyle1);
body.appendParagraph('Test title').setAttributes(titleStyle2);
body.appendParagraph('Test title').setAttributes(titleStyle3);
body.appendParagraph('Test title').setAttributes(titleStyle4);

Notice that in the last case, only when DocumentApp.Attribute.HEADING isn't set that the other styles are taken into account. I wonder why.

Instead of setting the font COMIC_SANS_MS every time for every added text, is there a way to set a default font (among other attributes) for the newly created document?

The following code does change nothing:
```
var docStyle = {};
docStyle[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.COMIC_SANS_MS;

var body = doc.getBody();
body.setAttributes(docStyle);
```

Best Answer

There are several add-ons for Google Documents and Sheets that already do that. One of them is Autocrat.

Some of these add-ons work on "merge" model: a template and data source.

The template holds the formatting, fixed text and placeholders for variable text. The spreadsheet is used as the data source, sometimes it has the menu options to trigger the merge function.

References
Overview of add-ons - Docs editors Help

Related Solutions

Google Sheets – How to Mail Merge from Google Spreadsheet to Google Document

You will need to write a Google Apps Script for that. You could let the first row of the spreadsheet be field names, and create a template document where the fields are referenced like [FIELD].

So if your spreadsheet looks like:

NAME  |  STREET             | ZIP    | TOWN
---------------------------------------------
Vidar | Karl Johans gate 15 | 0200   | Oslo
John  | 3021 Arlington Road | 123456 | Memphis, TN

... you could have a template document like

Dear [NAME], living at [STREET], [TOWN] [ZIP] ...

Your script will need to create a new, empty document, and for each row in your spreadsheet, add a new page and search/replace the field placeholders with row values.

I have a somewhat working version, which might need some polishing. It can be invoked here. It will create a new document named Result of mail merge.

You could use it as a starting point for your own script. Let me know if you're into that, or I can spend some more time finishing the script.

Script content:

var selectedTemplateId = null;
var selectedSpreadsheetId = null;
var spreadsheetDocPicker = null;
var templateDocPicker = null;

function mailMerge(app) {
  var app = UiApp.createApplication().setTitle("Mail Merge");
  templateDocPicker = createFilePicker(app, "Choose template", 
         UiApp.FileType.DOCUMENTS, "templateSelectionHandler"); 
  templateDocPicker.showDocsPicker();
  return app;
};

function createFilePicker(app, title, fileType, selectionHandlerName) {
  Logger.log("Creating file picker for " + fileType);
  var docPicker = app.createDocsListDialog();
  docPicker.setDialogTitle(title);
  docPicker.setInitialView(fileType);
  var selectionHandler = app.createServerHandler(selectionHandlerName);
  docPicker.addSelectionHandler(selectionHandler);
  return docPicker;
}

function templateSelectionHandler(e) {
  var app = UiApp.getActiveApplication();
  selectedTemplateId = e.parameter.items[0].id;
  UserProperties.setProperty("templateId", e.parameter.items[0].id);
  Logger.log("Selected template: " + selectedTemplateId);
  var spreadsheetDocPicker = createFilePicker(app, "Choose spreadsheet", 
        UiApp.FileType.SPREADSHEETS, "spreadsheetSelectionHandler");
  spreadsheetDocPicker.showDocsPicker();
  return app;
}

function spreadsheetSelectionHandler(e) {
  var app = UiApp.getActiveApplication();
  UserProperties.setProperty("spreadsheetId", e.parameter.items[0].id);
  selectedSpreadsheetId = e.parameter.items[0].id;
  Logger.log("Selected spreadsheet: " + selectedSpreadsheetId);
  doMerge();
  return app;
}

function doMerge() {
  var selectedSpreadsheetId = UserProperties.getProperty("spreadsheetId");
  var selectedTemplateId = UserProperties.getProperty("templateId");
  Logger.log("Selected spreadsheet: " + selectedSpreadsheetId);
  var sheet = SpreadsheetApp.openById(selectedSpreadsheetId);
  Logger.log("Spreadsheet opened");
  Logger.log("Opening template: " + selectedTemplateId);
  var template = DocumentApp.openById(selectedTemplateId);
  Logger.log("Template opened");
  var templateFile = DocsList.getFileById(selectedTemplateId);
  var templateDoc = DocumentApp.openById(templateFile.getId());
  //var mergedFile = templateFile.makeCopy();
  var mergedDoc = DocumentApp.create("Result of mail merge");
  var bodyCopy = templateDoc.getActiveSection().copy();
  Logger.log("Copy made");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var fieldNames = values[0];

  for (var i = 1; i < numRows; i++) {
    var row = values[i];
    Logger.log("Processing row " + i + " " + row);
    var body = bodyCopy.copy();
    for (var f = 0; f < fieldNames.length; f++) {
      Logger.log("Processing field " + f + " " + fieldNames[f]);
      Logger.log("Replacing [" + fieldNames[f] + "] with " + row[f]);
      body.replaceText("\\[" + fieldNames[f] + "\\]", row[f]);
    }
    var numChildren = body.getNumChildren();
    for (var c = 0; c < numChildren; c++) {
      var child = body.getChild(c);
      child = child.copy();
      if (child.getType() == DocumentApp.ElementType.HORIZONTALRULE) {
        mergedDoc.appendHorizontalRule(child);
      } else if (child.getType() == DocumentApp.ElementType.INLINEIMAGE) {
        mergedDoc.appendImage(child);
      } else if (child.getType() == DocumentApp.ElementType.PARAGRAPH) {
        mergedDoc.appendParagraph(child);
      } else if (child.getType() == DocumentApp.ElementType.LISTITEM) {
        mergedDoc.appendListItem(child);
      } else if (child.getType() == DocumentApp.ElementType.TABLE) {
        mergedDoc.appendTable(child);
      } else {
        Logger.log("Unknown element type: " + child);
      }
   }
   Logger.log("Appending page break");
   mergedDoc.appendPageBreak();
   Logger.log("Result is now " + mergedDoc.getActiveSection().getText());
  }
}

function testMerge() {
  UserProperties.setProperty("templateId", 
    "1pAXWE0uklZ8z-O_Tejuv3pWSTiSv583ptUTGPt2Knm8");
  UserProperties.setProperty("spreadsheetId", 
    "0Avea1NXBTibYdFo5QkZzWWlMYUhkclNSaFpRWUZOTUE");
  doMerge();
}


function doGet() {
  return mailMerge();
}

Google-drive – Why do the Google Drive’s equations look bad

If what you see in the image below does not look bad to you then I suggest because you are not using a suitable style. Entry was "nothing special" (standard entry with default formatting):

WA48719 example

but the formulae are Title style.

The first inserts Equation only for the x (the = 30 is just standard text).
The second has the text in parentheses is Normal style
The third has a font sized reduced from the default and x = 25 entered outside the equation.
The fourth has a Normal style x (but not for the upper limit of the sum range)

Edit.1:

Best Answer

Related Solutions

Google Sheets – How to Mail Merge from Google Spreadsheet to Google Document

Google-drive – Why do the Google Drive’s equations look bad

Related Topic