Script To Write Auto-Gen'd Google Doc URL To Spreadsheet-CodePudding

I have a Google Form that does two things upon hitting the Submit button. First, it dumps that data into a Spreadsheet, then it autofills a Google Doc Template with the info from the Form.

In my script to autofill the Google Doc, I've grabbed the URL for the Google Doc. But I need to write this URL into the last row of Column J in my Google Sheet. Also this is a shared Google Sheet, so I can't use the getActiveSheet function, I'd need to reference the Google Sheet URL, I believe.

Can anyone assist with this? Here's a snippet of the script to get the URL:

function autoFillGoogleDocFromForm(e) {
  //e.values is an array of form values
  var TimeStamp = e.values[0];
  var Technician = e.values[1];
  var Vendor = e.values[2];
  var xxx = e.values[3];
  var yyy = e.values[4];
  var SerialNumber = e.values[5];
  var AssetTag = e.values[6];
  var TicketNumber = e.values[7];
  var HostName = e.values[8];
  var DocumentLink = e.values[9];
  var Return = e.values[10];
  var Platform = e.values[11];
  var Summary = e.values[12];
  var URL = "";
    
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('aaa'); 
  
  //Put auto filled Google Doc into the appropriate Vendor Folder
  //file.makeCopy will return a Google Drive file object
  if (Vendor == "111") {
  var folder = DriveApp.getFolderById('bbb')
  var copy = file.makeCopy(TicketNumber   ' - '   SerialNumber, folder); 
  }
  
  if (Vendor == "222") {
  var folder = DriveApp.getFolderById('ccc')
  var copy = file.makeCopy(TicketNumber   ' - '   SerialNumber, folder); 
  }
   
  if (Vendor == "333") {
  var folder = DriveApp.getFolderById('ddd')
  var copy = file.makeCopy(TicketNumber   ' - '   SerialNumber, 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()); 

  //Get the url of the newly created Google Doc
  var url = doc.getUrl();
  //Script to write this URL into the Shared Google Sheet will go here

  //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('{{EmailAddress}}', Technician);
  body.replaceText('{{TicketNumber}}', TicketNumber);  
  body.replaceText('{{HostName}}', HostName); 
  body.replaceText('{{SerialNumber}}', SerialNumber); 
  body.replaceText('{{AssetTag}}', AssetTag); 
  body.replaceText('{{Summary}}', Summary); 
  body.replaceText('{{Vendor}}', Vendor); 
  body.replaceText('{{URL}}', url); 


  //Lastly we save and close the document to persist our changes
  doc.saveAndClose();

Thanks in advance!

CodePudding user response：

From your following situation in your question,

Also this is a shared Google Sheet, so I can't use the getActiveSheet function, I'd need to reference the Google Sheet URL, I believe.

If you have the permission to write the values to the shared Google Spreadsheet, how about the following modification?

From:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here

To:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here

var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
sheet.appendRow([url]);

In this modification, please set the Spredsheet ID and sheet name. By this, the value of url is appended to the next row of the last row of the sheet in the Google Spreadsheet.
If you want to use the Spreadsheet URL instead of Spreadsheet ID, please modify openById("### Spreadsheet ID ###") to openByUrl("### Spreadsheet URL ###").

References:

CodePudding user response：

Suggestion:

If you need to write the URL value into the last row of Column J in a shared Google Spreadsheet file, you can try this sample below:

function sample() {
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/SHEET_ID_IS_HERE/edit#gid=0');
  var sheet = ss.getSheets()[0]; // access first sheet
  var activeSheet = ss.setActiveSheet(sheet);

  //Sample setValue() action to the shared sheet file
  activeSheet.getRange("A1").setValue("URL");
}

NOTE: You would need to have an editor permission on the Shared Google Sheet file that you're accessing