0

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); 
}

0 Answers0