CODEWITHSUNDEEP
google-apps-script
chad h.
chad h.

Reputation: 111

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

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).

Upvotes: 0

Views: 468

Answers (2)

Wim den Herder
Wim den Herder

Reputation: 1305

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 !

Upvotes: 1

ScampMichael
ScampMichael

Reputation: 3728

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.

Upvotes: 0

Related Questions