Create a Google document from a script bound to a Google spreadsheet

Question

I am trying to create a document from data that is stored inside of a Google Spreadsheet. I have created a function in a bound script (to the spreadsheet), that when triggered, should take the appropriate data and put it into a new document. However, I get a permissions error when I try it.

Can you create a google document (in your google drive) from a script bound to a spreadsheet? If so, how?

Here are some steps to duplicate the behavior:

From a basic google sheet:

 |     A      |     B     |   C   |    D     |
1| First Name | Last Name | Ready | Function |
2| John       | Doe       |       |          |
3| Jane       | Doe       |       |          |

I created the following function using the script editor:

function createDocument(ready, row) {
  if(ready) {
    var data = Utilities.formatString("Hello %s %s", row[0][0], row[0][1]);
    var doc = DocumentApp.create("Testing");
    var body = doc.getBody();
    body.appendParagraph(data);
    return "Success"
  }
}

D2 has the following call:

 =createDocument(C2, A2:B2)

But when I enter "TRUE" into C2 (to trigger the creation of the document) I get the following error:

Error you do not have permission to call create (line 4).

Wim den Herder · Accepted Answer · 2015-08-08 20:07:59Z

You can solve this by using triggers in the script.

You can use onOpen (menu) or onEdit, which have their own advantages. You can also use both :)

onEdit: Immediate action on one row. (one row at a time)

onOpen: Action on multiple rows together, on request via menu.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Your Menu")
    .addItem("Process names", "spreadsheetToDoc")
    .addToUi();
}

function spreadsheetToDoc() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var values = range.getValues();
  var reg = /^true$/i;
  for(var i = 0; i < values.length; i++) {
    if(reg.test(values[i][2])) {
      createDocument(values[i]);
      range.getCell(i+1,4).setValue("Success");
      range.getCell(i+1,3).setValue("");
    }
  }
}

function createDocument(row) {
    var data = Utilities.formatString("Hello %s %s", row[0], row[1]);
    var doc = DocumentApp.create("Testing");
    var body = doc.getBody();
    body.appendParagraph(data);
}

The onEdit trigger checks if the value of column C has changed in the active row.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var reg = /^true$/i;
  var i = sheet.getActiveCell().getRow() - 1;

  if(reg.test(values[i][2])) {
    createDocument(values[i]);
    range.getCell(i+1,3).setValue("");
    range.getCell(i+1,4).setValue("Success");
  }
}

function createDocument(row) {
    var data = Utilities.formatString("Hello %s %s", row[0], row[1]);
    var doc = DocumentApp.create("Testing");
    var body = doc.getBody();
    body.appendParagraph(data);
}

You will have to add the triggers manually (go to project triggers via menu).

Good luck !

ScampMichael · Accepted Answer · 2015-08-08 19:01:40Z

0

Custom functions such as you have created will only return a value to the cell in which they reside and adjacent cells if they return an array, nothing more.

To execute your script you need to use some other method to trigger it such as a custom menu or and onEdit trigger.

answered Aug 8, 2015 at 19:01

ScampMichael

3,7382 gold badges19 silver badges23 bronze badges

Collectives™ on Stack Overflow

Create a Google document from a script bound to a Google spreadsheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related