Want the link of the newly created template folder in google drive to add in the specific cell of go-CodePudding

I have a script that creates the client's folder, subfolders, and sheets in the drive whenever I add a new client to the sheet through HTML form. I am looking for a way to add the link in column K for that newly created folder, and also share the link to that folder on the client's email with edit access.

Here is the code that adds template folders in the drive. By the way am new to apps script. So am just learning and taking guidance from experts. Any help is warmly thanked.

function createClientAccountFolder(clientNamefolder, costingSheetId, ProposalDocId, renamesheet) {
  // This is the root directory where all the client folders would be stored
  const customerRoot = DriveApp.getFolderById('folder_id');

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var databaseSheet = ss.getSheetByName("Clients");
 var lastAvailableRow = databaseSheet.getLastRow();
 const clientName = databaseSheet.getRange(lastAvailableRow, 2).getValue();

 if (clientNamefolder) {
  // This creates the main folder with the customer name
  const clients_sheet = ss.getRange("K2:K"   lastAvailableRow);
  const mainFolder = customerRoot.createFolder(clientNamefolder);
  var folder_url = mainFolder.getUrl();

  // have tried a way to add the link in cell
  clients_sheet.setFormula('=HYPERLINK("'   mainFolder.getUrl()   '")');

  

  // Creating the sub folders
  // Some are assigned to variables so that later children can be added
  const action_tracker =  mainFolder.createFolder("[SEO] - Action Tracker ["   clientNamefolder   "]")
  const audit =  mainFolder.createFolder("[SEO] - Audits ["   clientNamefolder   "]")
  

  // Creating children
  content.createFolder("["   clientName    "] Content Approved")
  content.createFolder("["   clientName    "] Content Draft")


  // Getting the documents
  const action_tracker_sheet = DriveApp.getFileById('Sheet_id')
  const analytics_audit = DriveApp.getFileById('Sheet_id')


  // Moving the documents to the respective folders
  const moved_action_tracker_sheet = action_tracker_sheet.moveTo(action_tracker)
  const moved_analytics_audit = analytics_audit.moveTo(audit)
 
  // renamin sheets
  moved_action_tracker_sheet.SpreadsheetApp.getActiveSpreadsheet().rename(clientName   " Action Tracker");
  moved_analytics_audit.SpreadsheetApp.getActiveSpreadsheet().rename(clientName   " Analytics Audit");


 } else {
   Logger.log('No Client Name Specified')
 }
}

This function is finally triggered to add folder, subfolders, and sheets.

function test(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var databaseSheet = ss.getSheetByName("Clients");
 var lastAvailableRow = databaseSheet.getLastRow();
 const clientName = databaseSheet.getRange(lastAvailableRow, 2).getValue();
 createClientAccountFolder(clientName, "[SHEET_ID]", "[DOC_ID]")
}

CodePudding user response：

Main issues and solution:

If you just want to display the link, there's no need to add a formula, you can just set the value directly via Range.setValue(folderUrl).
If you want the displayed text to be different from the link itself, use the approach taken in DrDaNkk's answer, via RichTextValue.
You only want to set the value of a single cell (column K of the last row). Therefore, your range should not include multiple cells, as in your current code ("K2:K" lastAvailableRow), but one, like "K" lastRow.
In order to share the folder with an email address with edit access, use Folder.addEditor(emailAddress).

Code sample:

function addLinkAndShare() {
  const customerRoot = DriveApp.getFolderById('folder_id');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const databaseSheet = ss.getSheetByName("Clients");
  const lastRow = databaseSheet.getLastRow();
  const clientName = databaseSheet.getRange(lastRow, 2).getValue();
  if (clientName) {
    const mainFolder = customerRoot.createFolder(clientNameFolder);
    const folderUrl = mainFolder.getUrl();
    databaseSheet.getRange("K"   lastRow).setValue(folderUrl);
    const clientEmail = databaseSheet.getRange("J"   lastRow).getValue();
    mainFolder.addEditor(clientEmail);
    /*
    / Creating subfolders, renaming, etc. (unrelated to your question)
    */
  } else {
    Logger.log('No Client Name Specified')
  } 
}

Other issues & improvements:

I'd suggest you to create the folder, add the folder URL and share it on the same script in which the data is appended to the sheet, right before doing that. This way, you can add all values at once and there's no need to retrieve the values that have just been set (e.g. clientName), and you'll reduce the amount of calls necessary (see Minimize calls to other services).

Since you didn't provide the code related to the data being appended to the sheet, though, I didn't add that to the code sample above.

CodePudding user response：

I want you to realize you're attempting to add the same folder link to multiple cells in a sheet. I don't know if that's purposeful but I think you might want to change that range. Anyways, my suggested solution uses the RichTextValue object so you don't have to use a formula. You'll want to change

clients_sheet.setFormula('=HYPERLINK("'   mainFolder.getUrl()   '")');

var richText = SpreadsheetApp.newRichTextValue().setText("Folder URL").setLinkUrl(folder_url).build();
clients_sheet.setRichTextValue(richText);