I want to create a google document based on google form submissions and then grab the link to that document and input it on the response line in the responses sheet. I have a script that almost works...
The problem is, it creates an additional doc that is blank and pastes the link to that doc in responses sheet.
I'm a beginner at coding so I'm sure it's something simple I'm missing
This is what I'm using to create the google doc
var file = DriveApp.getFileById('1SDy0aZ_cO2DWkfx3iMxTu5F1IxAfAvfUmHrOKKOObNE');
//file.makeCopy will return a Google Drive file object
var folder = DriveApp.getFolderById('1qLF3KKsWoiAnjqmQ-IBCzYIvU1nmeZpK')
var copy = file.makeCopy(site + ', ' + 'Daily Check' + ' '+ date, folder);
var doc = DocumentApp.openById(copy.getId());
var docurl = copy.getUrl()
var body = doc.getBody();
doc.saveAndClose();
This is what I'm using to include the link to the created doc in the responses sheet
var responseSheet = e.range.getSheet()
var row = e.range.getRow();
var responseColumn = 32; // Column where the response URL is recorded.
responseSheet.getRange(row, responseColumn).setValue(docurl);
When I tested creating the google doc section of the code there was no problem. It's only when I added the code to paste the link in the sheet that I started getting the problem
Below is the full code if that makes it easier...
function myFunction(e) {
var date = e.values[0];
var site = e.values[2];
var item1= e.values[17];
var item2 = e.values[18];
var item3 = e.values[19];
var item4 = e.values[20];
var item5 = e.values[21];
var item6 = e.values[22];
var item7 = e.values[23];
var item8 = e.values[24];
var item9 = e.values[25];
var item10 = e.values[28];
var emailaddress = e.values[1];
var othercomments = e.values[29];
var workorder = e.values[26];
var repairmade = e.values[27];
var a1 = e.values[3];
var a2 = e.values[4];
var a3 = e.values[5];
var a4 = e.values[6];
var a5 = e.values[7];
var a6 = e.values[8];
var a7 = e.values[9];
var a8 = e.values[10];
var a9 = e.values[11];
var a10 = e.values[12];
var a11 = e.values[13];
var a12 = e.values[14];
var a13 = e.values[15];
var a14 = e.values[16];
var classroom= a1+a2+a3+a4+a5+a6+a7+a8+a9+a10+a11+a12+a13+a14
var file = DriveApp.getFileById('1SDy0aZ_cO2DWkfx3iMxTu5F1IxAfAvfUmHrOKKOObNE');
var folder = DriveApp.getFolderById('1qLF3KKsWoiAnjqmQ-IBCzYIvU1nmeZpK')
var copy = file.makeCopy(site + ', ' + 'Daily Check' + ' '+ date, folder);
var doc = DocumentApp.openById(copy.getId());
var docurl = copy.getUrl()
var body = doc.getBody();
body.replaceText('{{timestamp}}', date);
body.replaceText('{{site}}', site);
body.replaceText('{{classroom}}', classroom);
body.replaceText('{{emailaddress}}', emailaddress);
body.replaceText('{{item1}}', item1);
body.replaceText('{{item2}}', item2);
body.replaceText('{{item3}}', item3);
body.replaceText('{{item4}}', item4);
body.replaceText('{{item5}}', item5);
body.replaceText('{{item6}}', item6);
body.replaceText('{{item7}}', item7);
body.replaceText('{{item8}}', item8);
body.replaceText('{{item9}}', item9);
body.replaceText('{{item10}}', item10);
body.replaceText('{{workorder}}', workorder);
body.replaceText('{{repairmade}}', repairmade);
body.replaceText('{{othercomments}}', othercomments);
doc.saveAndClose();
var responseSheet = e.range.getSheet()
var row = e.range.getRow();
var responseColumn = 32;
responseSheet.getRange(row, responseColumn).setValue(docurl);
}