Recently, a close friend of mine asked me to help him with writing a Google Apps Script that exports row data from Google Sheets to Google Docs. His exact use case was that he needed to create invoices in Google Docs, using the data that he has in Google Sheets. Being the pragmatic (:P) programmer that I am, I Googled for some solutions first, and came across one by Open Source Hacker and a fork of it called GoDDaMn.

Open Source Hacker’s version replaces the entire paragraph in which the “keyword” is found, while GoDDaMn provides string replacement (instead of paragraph replacement), and I could not get GoDDaMn to work correctly for me, so I started writing my own version based on theirs, so huge thanks to them for providing a foundation :)! I also made a sample spreadsheet and a sample document template so it’d be easier if you want to adapt this for your own use case.

What you’ll need

1. A Google Sheets spreadsheet containing the data. Sample spreadsheet here.
2. A Google Apps Script that will be part of the above spreadsheet. Code is available below.
3. A Google Docs template that will serve to create new documents from the data in the spreadsheet. Sample template here.
4. A Google Drive folder that will store the newly created documents.

Create (or make a copy of the above samples) a Google Sheets spreadsheet, a Google Docs template, and a Google Drive folder. You will then need the IDs of the spreadsheet, the template, and the Google Drive folder. You can retrieve them from their URLs, as seen below.

Google Sheets

Google Docs

Google Drive

To use the Google Apps Script below, go to your spreadsheet, and then click on Tools -> Script editor.. as shown below.

If you have cloned the sample spreadsheet above, the code may already be in it. If not, copy and paste the script below and save the script. The comments in the script should explain what it’s doing, but the gist of it should be as follows:

1. Replacing the values SOURCE_TEMPLATE, RENTAL_SPREADSHEET and TARGET_FOLDER with your IDs (highlighted below).
2. Updating/changing rows 57 to 59 of the script which determines the filename of the created Google Docs document (highlighted below).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

var row_ID = "";
var SOURCE_TEMPLATE = "1WptU_0Swrc3VLxKPCS_xHcvV4YdD6_a4FbOeVTQz79Y"; //Replace this with your Google Docs Template ID
var DATA_SPREADSHEET = "1befBe_dYdx5JEg8s6D78FYqXoDzeQh6DBkmNRoCy7IQ"; //Replace this with your Google Sheets (Source) ID
var TARGET_FOLDER = "0BzhgI6paOjCrS0xDUzc4eGxWVEU"; //Replace this with your Google Drive Folder ID

//Create Custom Menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
      .addItem('Output Row to Docs', 'outputToDocs')
      .addToUi();
}

function getRowAsArray(sheet, row) {
  var lastColumn = sheet.getLastColumn();
  var dataRange = sheet.getRange(row, 1, 1, lastColumn);
  var data = dataRange.getValues();
  var columns = [];

  for (i in data) {
    var row = data[i];

    for(var l=0; l<lastColumn; l++) {
        var col = row[l];
        columns.push(col);
    }
  }

  return columns;
}

function createDuplicateDocument(sourceId, name) {
    var source = DriveApp.getFileById(sourceId);
    var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
    var newFile = source.makeCopy(name, targetFolder);
    return DocumentApp.openById(newFile.getId());
}

function outputToDocs() {

  var data = SpreadsheetApp.openById(DATA_SPREADSHEET);

  if(!row_ID) {
    row_ID = SpreadsheetApp.getActiveSheet().getActiveSelection().getRowIndex(); //Row index starts at 1

    if (row_ID == 1) { //If we are on row 1 (column headers)
      var row_ID = Browser.inputBox("Enter row number to output", Browser.Buttons.OK_CANCEL);
    }
  }

  var sheet = data.getSheets()[0];
  var columnHeaders = getRowAsArray(sheet, 1);
  var rowData = getRowAsArray(sheet, row_ID);

  // This portion can be customized. In my use case I wanted to create the Google Docs using a person's name and a date.
  // I'm using the column index directly, probably the only part in the code that is hardcoded.
  var pName = rowData[3];
  var orderDate = Utilities.formatDate(rowData[0], SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyy-MM-dd");
  var target = createDuplicateDocument(SOURCE_TEMPLATE, orderDate + " - " + pName + " Data");

  for(var i=0; i < columnHeaders.length; i++) {
    var key = ":" + columnHeaders[i] + ":";
    var text = rowData[i];
    var body = target.getBody();
    if (text instanceof Date) {
      text = Utilities.formatDate(text, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyy-MM-dd");
    }
    body.replaceText(key, text);
  }

}

Once the above is done, refresh your spreadsheet. You should then see a Custom menu item. The only step left to do is to make sure that your template is updated to reflect the same column names as your spreadsheet, but surround by “:“. So if you have a column in your spreadsheet called Data, and you want the content from it to be populated into your document, your template should have :Data:.

You are now ready to output the data in your spreadsheet by clicking on Custom -> Output Row to Docs, as seen below.

A new document will be created in your Google Drive folder with the data from the current row. If the header row (row 1) is selected, it will prompt you to enter a row number. The overall flow should look like the following:

Overall Flow

That’s all! Please leave any questions or suggestions you may have in the comments below.