google-sheetsgoogle-apps-script

How can I save the URL(s), after making a copy (event) of a Google Sheets spreadsheet?

Overview: I have a Google Sheets template file which contains multiple tabs. When the file is opened, the user is prompted with a pop-up message to "Click link to create a Copy --> My Workbook copy". After the "My Workbook copy" is selected, the file is saved to a specific Google Drive folder, and a unique/new Google Spreadsheet file opens in which they can make edits.

Goal: I'm attempting to save the URL Links from the New Google Sheets file(s) each time a copy has been made (to the Reference tab). My goal is to have the URL link(s) added to the Reference sheet, in column C.

Current issue: The makeAcopy script (makes a copy of the spreadsheet and adds the file to the specified Google Drive), but the URL Link is not being added to the Reference sheet. I'm not sure if I'm taking the right approach, unclear on whether the logic for adding the URL links to Reference sheet needs to be added in a separate event (On Open) or if it should be included in the makeAcopy script. Further guidance would be must appreciated it.

function onOpen(e){

const info = e.namedValues;
const blob = makeAcopy(info);
const date = Date;
const entryRow = e.range.getRow();
SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,3).setValue(blob);
SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,2).setValue(fileName);
SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,1).setValue(date);

}



function makeAcopy(info){

    var nom = 'My Workbook copy'
        var sheet = SpreadsheetApp.openById('1EylqqfqXWaSCJbvVmqrwJHcwGPUvlQYOZ0wSpvT_mMY')
        var destFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos"); //.next();
  
    const ds = Utilities.formatDate(new Date(),SpreadsheetApp.getActive().getSpreadsheetTimeZone(),"MM.dd.yyyy");
  //var fileName = DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder).setName(`${nom}.${ds}`);
  var fileID = DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder).getId();


    var htmlOutput = HtmlService
        .createHtmlOutput('<a href="https://docs.google.com/spreadsheets/d/' + fileID + '/" target="_blank">' + nom + '</a>')
        .setWidth(350) //optional
        .setHeight(50); //optional
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Click link to create a Copy');

    return fileID;
    //return fileName;

}

Dummy Spreadsheet

Solution

  • You may add the following to the makeAcopy script to achieve what you'd like to do:

      var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference");
  var lr = ss.getLastRow() + 1;
  ss.getRange(lr, 1).setValue(ds);
  ss.getRange(lr, 2).setValue(nom);
  ss.getRange(lr, 3).setValue('https://docs.google.com/spreadsheets/d/' + fileID + '/edit#gid=0');

    The full code would become:

    function onOpen(e) {

  const info = e.namedValues;
  makeAcopy(info);

}



function makeAcopy(info) {

  var nom = 'My Workbook copy'
  var sheet = SpreadsheetApp.openById('1EylqqfqXWaSCJbvVmqrwJHcwGPUvlQYOZ0wSpvT_mMY')
  var destFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");

  const ds = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM.dd.yyyy");
  var fileID = DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder).getId();


  var htmlOutput = HtmlService
    .createHtmlOutput('<a href="https://docs.google.com/spreadsheets/d/' + fileID + '/" target="_blank">' + nom + '</a>')
    .setWidth(350)
    .setHeight(50);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Click link to create a Copy');

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference");
  var lr = ss.getLastRow() + 1;
  ss.getRange(lr, 1).setValue(ds);
  ss.getRange(lr, 2).setValue(nom);
  ss.getRange(lr, 3).setValue('https://docs.google.com/spreadsheets/d/' + fileID + '/edit#gid=0');

  return fileID;

}

    Note: I've removed const date = Date;, const entryRow = e.range.getRow();, SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,3).setValue(blob);, SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,2).setValue(fileName);, and SpreadsheetApp.getActiveSheet().getSheetName("Reference").getRange(entryRow,1).setValue(date); from onOpen(e) and did not change the other parts of the code to keep its integrity.

    OUTPUT

    image

    Keep in mind that the makeCopy(name, destination) method copies the entire file, so if there are already links generated by others in the Reference sheet, they'll also be copied.