Google Script help to create new folder in drive with name drawn from two cells in one row-CodePudding

I am trying to create a new folder in google drive using a script attached to a button in google sheets. I have done things so far that I am able to create a folder. My request is for help to name the folder using values from two cells in the row. For example: Serial No and Customer Name are two columns in the sheet. So in that row when I click on the create folder button, I want the name of the folder created to be Serial No-Customer Name (say, 1-XYZ). I have searched a lot online for this but have not got anything that I can use. I am not a techie so I am finding it difficult to understand how this can be done. Any help is welcome. Thanks, Ravi

CodePudding user response：

Assuming that serial no is in A and customer name in B, and a chec k box anywhere in the same row

To create a new folder and name it

function onSpeEdit(event){
  var sheet = event.source.getActiveSheet();
  var cel = event.source.getActiveRange();
  if (cel.getValue()==true){
    var name =  sheet.getRange('A'   cel.getRow()).getValue()   '_'   sheet.getRange('B'   cel.getRow()).getValue()
    if (name!=''){
      sheet.getRange('F'   cel.getRow()).setValue(getFolderId(name))
    }
  }
}

function getFolderId(name){
  var id;
  var folders = DriveApp.getFoldersByName(name);
  if (folders.hasNext()) {
    var folder = folders.next();
    id = folder.getId(); }
  else {
    var folder = DriveApp.createFolder(name);
    id = folder.getId();
  }
  return id;
}

therefore I also prevent creating the same folder twice with the same name

put an installable trigger on onSpeEdit

you will get back the folder id in column F

DriveApp

installable trigger

CodePudding user response：

The problem is that the button/shape that is assigned to the script does not hold the location where you put it. It is not bound to a cell, it is floating over it.

First thing you might think is to create multiple buttons and create a function for each button, but that is very time consuming and worse, very bad implementation.

So we have to be creative to identify which cells we'd like to process. Here is what I have thought so you can identify which row to create a folder for.

You would need to add a single column in which it identifies if the row is to be processed or not. Once a checkbox is ticked and you pressed the button, the script now loops all the rows and finds the rows in which a checkbox is ticked. Then creates a folder based on the data it held. This also allows you to create multiple folders if multiple checkboxes are ticked. See the script and images below:

Script:

function createFolder() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  values.forEach(function (row) {
    var [isTicked, serialNum, customerName] = row;
    // if column A is ticked 
    if(isTicked) {
      var name = `${serialNum}-${customerName}`;
      var folders = DriveApp.getFoldersByName(name);
      // if name generated is not existing
      if(!folders.hasNext())
        DriveApp.createFolder(name);
    }
  });
}

Sample:

enter image description here

Output:

enter image description here