3

Before anyone says go look at other threads, I already checked out the following: Running Multiple Functions in Google Apps Script and How to execute multiple functions in google apps script in order? and am still stuck.

For reference, I have limited coding experience and have been teaching myself to do this one specific task and a couple other document auto fill app scripts (which work fine but are individual) for my job.

I have a google form for data ingest which auto fills a series of forms needed to complete an application at my company. Previously, you would have to manually input the data multiple times and I figured there had to be a better way.

The code takes inputs from the google form via a spreadsheet so we can keep track and auto fills a 3 pre generated docs which later get synced on to our network drive so all in office can access what they need.

Anyway, the code is as follows:

function fileFills(e){

  var address = e.values[1];
  var tenman = e.values[2];
  var tennum = e.values[3];
  var price = e.values[4];
  var wks = e.values[5];
  var deposit = e.values[6];
  var tname1 = e.values[7];
  var tname2 = e.values[8];
  var tname3 = e.values[9];
  var tname4 = e.values[10];
  var tname5 = e.values[11];
  var tname6 = e.values[12];
  var tname7 = e.values[13];
  var tname8 = e.values[16];
  var tname9 = e.values[17];

var file = DriveApp.getFileById('form template 1');
var parentFolder = DriveApp.getFolderById('main folder');
var newFolder = parentFolder.createFolder(address);
var copy = file.makeCopy(address + ', ' + ' Summary');
copy.moveTo(newFolder);

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

var body = doc.getBody();

body.replaceText('{{Add}}', address);
body.replaceText('{{TM}}', tenman);
body.replaceText('{{TTC}}', tennum);
body.replaceText('{{Price}}', price);
body.replaceText('{{Time}}', wks);
body.replaceText('{{Deposit}}', deposit);
body.replaceText('{{N1}}', tname1);
body.replaceText('{{N2}}', tname2);
body.replaceText('{{N3}}', tname3);
body.replaceText('{{N4}}', tname4);
body.replaceText('{{N5}}', tname5);
body.replaceText('{{N6}}', tname6);
body.replaceText('{{N7}}', tname7);
body.replaceText('{{N8}}', tname8);
body.replaceText('{{N9}}', tname9);
  
var file2 = DriveApp.getFileById('form template 2');
var copy2 = file2.makeCopy(address + ', ' + ' Key Sign');
copy2.moveTo(newFolder);

var doc2 = DocumentApp.openById(copy2.getId());

var body2 = doc2.getBody();

body2.replaceText('{{Add}}', address);
body2.replaceText('{{TTC}}', tennum);
body2.replaceText('{{Time}}', wks);
body2.replaceText('{{N1}}', tname1);
body2.replaceText('{{N2}}', tname2);
body2.replaceText('{{N3}}', tname3);
body2.replaceText('{{N4}}', tname4);
body2.replaceText('{{N5}}', tname5);
body2.replaceText('{{N6}}', tname6);
body2.replaceText('{{N7}}', tname7);
body2.replaceText('{{N8}}', tname8);
body2.replaceText('{{N9}}', tname9);
  
var file3 = DriveApp.getFileById('form template 3');
var copy3 = file3.makeCopy(address + ', ' + ' Tenancy Summary');
copy3.moveTo(newFolder);

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

var body3 = doc3.getBody();

body3.replaceText('{{Add}}', address);
body3.replaceText('{{TM}}', tenman);
body3.replaceText('{{TTC}}', tennum);

}

Obviously I have removed file and folder IDs above.

The issue is that when I submit a test result through the form, it generates a new folder with the correct address, it then copies the first doc into it and fills it, but that is as far as it gets. I get no 2nd or 3rd form.

I had a version previously where I managed to get it to make copies of the 2nd and 3rd form as well, but only into the parent folder and it would not fill the data or move it to the correct folder.

Please help! I am sure its something obvious that I'm missing but it's driving me crazy!

Thanks!

MeatOven
  • 33
  • 4
  • I see no save and close commands in your script. https://developers.google.com/apps-script/reference/document/document#saveAndClose() Not sure if it's a cause of the problem, but anyway. It makes sense to try to save and close every doc. – Yuri Khristich Jul 19 '21 at 13:17
  • Hi Yuri, thanks for the response. I did originally have save and close commands in there, but I took them out wondering if they stopped it working! Additionally, I read somewhere (though it escapes me where exactly) that it automatically saves and closes and docs that have been opened during the script execution anyway, so I figured they weren't needed. Will try again with them anyways to see if it helps :) EDIT: can confirm, did not fix the issue :( – MeatOven Jul 19 '21 at 13:21
  • Ok. Another thing: `var doc3 = DocumentApp.openById(copy.getId());` there should be `var doc3 = DocumentApp.openById(copy3.getId());` But it has nothing to do with doc1 and doc2, though. I'm trying to reproduce the problem and it seems work fine for now. – Yuri Khristich Jul 19 '21 at 13:48
  • I copied your script, substituted the event object, run it from Script Editor an everything work fine. It creates subfolder 'address' and put three files (with replaced fields) generated from templates inside the folder. So, I suspect, the cause is not in the code but in the way how the code is running. How exactly you're calling the function 'fileFills()'? If there is a some trigger? – Yuri Khristich Jul 19 '21 at 13:57
  • Thanks again Yuri, I am calling it by the installable trigger "on form submit" so that when data is input to the google form, it triggers as soon as the data is placed on the google sheet where the app script resides. Hope that helps :) – MeatOven Jul 19 '21 at 14:05
  • 1
    OK, I discovered the issue! It looks like on each of the first 2 documents, when it gets to replacing N8 and N9, it just gets stuck for some reason. It's the strangest thing. Can't replace it on Doc1 originally, so it stopped executing the script when it ran into that error. Removed 8&9 from first part of the script, ran it again, it did the same thing with second doc. Removed 8&9 from that one, ran it again, and it worked for all 3 docs!! Only issue is now that I need 8 and 9 to work, but I double checked all the docs and they're exactly the same format as the other numbers. Toughts? – MeatOven Jul 19 '21 at 14:20
  • Perhaps it has to do with skipped numbers: var tname**7** = e.values[**13**]; var tname**8** = e.values[**16**]; var tname**9** = e.values[**17**]; ? – Yuri Khristich Jul 19 '21 at 14:55
  • oh my god I don't believe it. You're right! I am so stupid! can't believe it was that, how did I manage to skip 2 numbers. I said in the OP that it would be something completely obvious that i had missed. Haha, thanks for everything! – MeatOven Jul 19 '21 at 15:25
  • 1
    Have you tried checking the Execution tab for errors? Usually, the error comes from the body.replaceText() when the substituting variable is undefined. This will stop the script which causes the other 2 files not to be created. – Nikko J. Jul 19 '21 at 15:28
  • I'm a noob in forms and triggers. I didn't check anything. It just ocurred me, why did you skip a couple fields of the form? It looks suspiciously. But it's impossible to be sure since I have no your real form. – Yuri Khristich Jul 19 '21 at 15:33
  • And it gives no errors with substituted event object. It's need a real form, real submitting, etc. – Yuri Khristich Jul 19 '21 at 15:35

1 Answers1

2
var tname4 = e.values[10];
var tname5 = e.values[11];
var tname6 = e.values[12];
var tname7 = e.values[13];
var tname8 = e.values[16]; // <-- 14?
var tname9 = e.values[17]; // <-- 15?

I think the last two lines should be:

var tname8 = e.values[14];
var tname9 = e.values[15];
Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23