Help combining scripts to create new folder + new template on form submission

[Ewan Farry]

Hi,

Hoping someone can help me connect two scripts below to perform a single function. 

My progress so far is;

1. > On Google form submission, create a folder - DONE
2. >> On same Google form submission, create copy of a google doc template - DONE
3. >>> Have the newly created Doc template added to the newly created folder - NEED HELP!

I have 1. as below

function createChannelFolder() {

 

// identify the sheet where the data resides

var ss = SpreadsheetApp.getActive();

var names = ss.getSheetByName("Completed Certifications");

 

//identify the cell that will be used to name the folder 

var ChannelName = names.getRange(names.getLastRow(), 2).getValue(); 

 

//identify the parent folder the new folder will be in

var parentFolder=DriveApp.getFolderById("18jbqtJ57IWcjfrwA7K3U9lFbIJrFlwhc");

 

//create the new folder

var newFolder=parentFolder.createFolder(ChannelName);

 

}

and 2. as below

function autoFillGoogleDocFromForm(e) {

  //e.values is an array of form values

  var Timestamp = e.values[0];

  var Channel = e.values[1];

  var Name = e.values[2];

  var Email = e.values[3];

  var Title = e.values[4];

  var Address = e.values[5];

  var Country = e.values[6];

  var Phone = e.values[7];

  var Website = e.values[8];

  var CountriesActive = e.values[9];;

  

  //file is the template file, and you get it by ID

  var file = DriveApp.getFileById('14_YgeroYctwWhWq-P7k0ayBR-PcOfQjMs2yHdkgREgs'); 

  

  //We can make a copy of the template, name it, and optionally tell it what folder to live in

  //file.makeCopy will return a Google Drive file object

  var folder = DriveApp.getFolderById('18jbqtJ57IWcjfrwA7K3U9lFbIJrFlwhc')

  var copy = file.makeCopy(Channel + ',' + Country, folder); 

  

  //Once we've got the new file created, we need to open it as a document by using its ID

  var doc = DocumentApp.openById(copy.getId()); 

  

  //Since everything we need to change is in the body, we need to get that

  var body = doc.getBody(); 

  

  //Then we call all of our replaceText methods

  body.replaceText('{{Timestamp}}', Timestamp); 

  body.replaceText('{{Channel}}', Channel);  

  body.replaceText('{{Name}}', Name);

  body.replaceText('{{Email}}', Email); 

  body.replaceText('{{Title}}', Title);  

  body.replaceText('{{Address}}', Address);

  body.replaceText('{{Country}}', Country); 

  body.replaceText('{{Phone}}', Phone);  

  body.replaceText('{{Website}}', Website);

  body.replaceText('{{CountriesActive}}', CountriesActive);

          

  

  //Lastly we save and close the document to persist our changes

  doc.saveAndClose(); 

}

Any help much appreciated!

Ewan

[Jean-Luc Vanhulst]

You're not sharing what of 2) you need help with. What is not working?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cd090524-7399-4c96-9c72-4014d4ec5a21o%40googlegroups.com.

--

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

[Ewan Farry]

Hi Jean-Luc,

Thank you for replying, I think I haven't made this clear.

Both the scripts I included work exactly as required there is no issue with them not working.

What I would like help with is if someone could advise on how to consolidate the two scripts into one, with the result being;

- A form submission creates a folder
- Within that folder, a document is created from a template

At the moment, I have;
- A form submission creating a folder
- A document created from a template in a specified folder (not the newly created one)

I hope this makes sense.

Ewan 

[Jean-Luc Vanhulst]

If you change the this one to return the new Folder object:

function createChannelFolder() {

 

// identify the sheet where the data resides

var ss = SpreadsheetApp.getActive();

var names = ss.getSheetByName("Completed Certifications");

 

//identify the cell that will be used to name the folder 

var ChannelName = names.getRange(names.getLastRow(), 2).getValue(); 

 

//identify the parent folder the new folder will be in

var parentFolder=DriveApp.getFolderById("18jbqtJ57IWcjfrwA7K3U9lFbIJrFlwhc");

 

//create the new folder

  return newFolder=parentFolder.createFolder(ChannelName);

 

}

and split the trigger function:  

function autoFillGoogleDocFromForm(e) {

   van newFolder = createChannelFolder();

   doc = createNewdoc();

   doc.moveTo(newFolder);

}

function createNewdoc {

  //e.values is an array of form values

  var Timestamp = e.values[0];

  var Channel = e.values[1];

  var Name = e.values[2];

  var Email = e.values[3];

  var Title = e.values[4];

  var Address = e.values[5];

  var Country = e.values[6];

  var Phone = e.values[7];

  var Website = e.values[8];

  var CountriesActive = e.values[9];;

  

  //file is the template file, and you get it by ID

  var file = DriveApp.getFileById('14_YgeroYctwWhWq-P7k0ayBR-PcOfQjMs2yHdkgREgs'); 

  

  //We can make a copy of the template, name it, and optionally tell it what folder to live in

  //file.makeCopy will return a Google Drive file object

  var folder = DriveApp.getFolderById('18jbqtJ57IWcjfrwA7K3U9lFbIJrFlwhc')

  var copy = file.makeCopy(Channel + ',' + Country, folder); 

  

  //Once we've got the new file created, we need to open it as a document by using its ID

  var newId = copy.getId();

  var doc = DocumentApp.openById(newId); 

  

  //Since everything we need to change is in the body, we need to get that

  var body = doc.getBody(); 

  

  //Then we call all of our replaceText methods

  body.replaceText('{{Timestamp}}', Timestamp); 

  body.replaceText('{{Channel}}', Channel);  

  body.replaceText('{{Name}}', Name);

  body.replaceText('{{Email}}', Email); 

  body.replaceText('{{Title}}', Title);  

  body.replaceText('{{Address}}', Address);

  body.replaceText('{{Country}}', Country); 

  body.replaceText('{{Phone}}', Phone);  

  body.replaceText('{{Website}}', Website);

  body.replaceText('{{CountriesActive}}', CountriesActive);

          

  

  //Lastly we save and close the document to persist our changes

  doc.saveAndClose(); 

  return newId;

}

than it seems like the only thing you need to combine the two:

 doc.moveTo( createChannelFolder() ) add the end 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5885cd37-9a72-4b6b-bf7c-b3ed4beed198n%40googlegroups.com.

[Ewan Farry]

Hi Jean-Luc,

Really appreciate the help, I have tried to implement the revised script as suggested but I am not having any luck.

I am a complete novice with Google scripts, and it's possibly that I am misunderstanding what you mean by "and split the trigger function:"  and also where you mean for doc.moveTo( createChannelFolder() ) to go.

I think unfortunately that i'd need the necessary steps to combine these scripts spelled out for me. If you have the time to provide further helpa dn point me in the right direction it would be greatly appreciated.

Many thanks,

Ewan

[Ewan Farry]

Manged to get the solution to create a new document in a new folder.

Still haven't worked out how a second empty folder being created can be avoided but the two scripts I have working are as below;

CreateChannelFolder():  

• This is the first trigger on form submission and creates a folder that in my case is named after a 'Channel Name' which is in row 2 of the Google sheet linked to the form.

function createChannelFolder() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("SHEETNAME");

var ChannelName = names.getRange(names.getLastRow(), 2).getValue(); 

var parentFolder=DriveApp.getFolderById("FOLDERID");
return parentFolder.createFolder(ChannelName); 

}

AutoFillGoogleDocFromForm(e):' 

• This is the second trigger on form submission and creates a copy of a template document and then fills in answers from a Google form based on the rows of the Google sheet linked to the form.

function autoFillGoogleDocFromForm(e) {
  //e.values is an array of form values  
  var Timestamp = e.values[0];
  var Channel = e.values[1];
  var Name = e.values[2];;  

  var file = DriveApp.getFileById('FILEID'); 
  var folder = createChannelFolder(); 
  var copy = file.makeCopy(Channel + ',' + Name, folder); 

  var newId = copy.getId();
  var doc = DocumentApp.openById(newId); 

  var body = doc.getBody(); 

  body.replaceText('{{Timestamp}}', Timestamp); 
  body.replaceText('{{Channel}}', Channel);  
  body.replaceText('{{Name}}', Name);        

  doc.saveAndClose(); 

[Jean-Luc Vanhulst]

You might want to look at your executions. The code above itself does not create two folders. But maybe if runs twice (you mention 'the second trigger') the first trigger might ALSO create a folder?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8981e62c-844c-4de0-8d01-60382a23d551n%40googlegroups.com.

[Ewan Farry]

You were spot on! I think I misunderstood triggers and had one set per function. 
Thanks very much for all your help!