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

Question

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. Correction: using the getActiveSheet functions are fine, I forgot this script is running from the (Active) Google Sheet (apologies!).

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!

Answer 1

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:

• openById(id)
• openByUrl(url)
• appendRow(rowContents)

Edit:

From the following replying,

That doesn't seem to work to insert the URL into the last row of column J unfortunately. It looks like this script can use the Active Sheet afterall, I'll edit the OP.

In this case, 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 ###");
var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
sheet.getRange("J" + (sheet.getLastRow() + 1)).setValue(url);

Or

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

// This is from https://stackoverflow.com/a/44563639/7108653
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
  const range = this.getRange(offsetRow, columnNumber, 2);
  const values = range.getDisplayValues();
  if (values[0][0] && values[1][0]) {
    return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  } else if (values[0][0] && !values[1][0]) {
    return offsetRow + 1;
  }
  return offsetRow;
};

// var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
sheet.getRange("J" + sheet.get1stEmptyRowFromTop(10)).setValue(url);

Answer 2

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

References:

• openByUrl(url)
• getSheets()
• setActiveSheet(sheet)

Answer 3

Here's the code that ended up working for me.

//Get the url of the newly created Google Doc
  var url = doc.getUrl();

  // var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
  var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
  sheet.getRange("J" + (sheet.getLastRow())).setValue(url);