Question 244290 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – API script to create a Google sheets file in a target folder

let me start by saying that I am very inexperienced with this type of coding so I apologize if any relevant details are missing.

I started developing an API script to run on a Google sheets file, the script as the objective of dividing al entries in this "originalFile" into several individual other Google sheets files based on the names column, and then send an automatic email to the recipients. Below is an example:

Turn this

enter image description here

into this, for each of the names

enter image description here

So far I’ve been able to do it but here is where it gets tricky. The email that is sent has a link to the file, but since the new files are created in the root folder of my GDrive and I don’t seem to be able to grant them access automatically in the API code (already tried with the DriveApp and could not get it to work).

My new approach would be to have the files be created in a target folder where everyone has permission to see/edit files, but I haven’t been able to do so so far. I’ll post below the code that I’ve gotten to work until now (it splits the original file and sends emails automatically).

    function createIndividualSheets(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var originalSheet=ss.getActiveSheet();
  var nameColumnRange=originalSheet.getRange("A2:A");
  var nameColumnValues=nameColumnRange.getValues();
  
  //create and object to keep track of the names
  var processedNames={};

  for(var i=0;i<nameColumnValues.length;i++){
    var name=nameColumnValues[i][0];

    //check if the cell is not empty and if the name has not been processed
    if(name!=="" && !processedNames[name]){

      //generates the new file's name using the current date and the name of the specific AM
      var date = new Date();
      var formattedDate =Utilities.formatDate(date, ss.getSpreadsheetTimeZone(),"yyyy-MM-dd");
      var newName=name+" "+formattedDate;
      
      //creates the new googlesheet document
      var newSpreadsheet=SpreadsheetApp.create(newName);
      
      //get the active sheet in the new document
      var newSheet = newSpreadsheet.getSheets()[0];

      //copy the data from the original sheet to the new sheet with a specific name
      var dataRange = originalSheet.getDataRange();
      var values=dataRange.getValues();
      var newData =[];
      newData.push(values[0]);

     for( var j = 1; j < values.length;j++){
      if(values[j][0]===name){
        newData.push(values[j]);
      }
     }

      //set the data to the new sheet
      newSheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);

      //mark the name as processed
      processedNames[name]=true;

      //send one email with all the entries corresponding to a certain name containing the link to the new document
      var emailSheet=newSpreadsheet.getSheetByName("Sheet1");
      var emailAddress=emailSheet.getRange("D3").getValue();

     if (emailAddress){
      var subject="Your new G sheet is ready";
      var body = "Dear" + name+",nnYour newsheet is attached.nnHere is the link:" + newSpreadsheet.getUrl();
      MailApp.sendEmail(emailAddress,subject,body);
     }

     //close the new documents
     Utilities.sleep(5000);
    }
  }
}

I tried creating the files directly in the target folder or moving them from the root folder to the target one but with no success as I ended up getting empty PDF files (don’t really understand why).

Any suggestions are welcomed 🙂

Tags:

2

Answers


  1. 0

    Not sure how you tried to create or move them to the destination folder, but one of the ways to do this can be to:

    1. Get the ID of the newly created spreadsheet (docs)
    const sheetId = newSpreadsheet.getId()
    1. Using the DriveApp class, get the File object for the sheetId (docs)
    const fileObject = DriveApp.getFileById(sheetId)

    1. Use the FOlder class to find the destination folder by name/id (docs)

    2. Once you get the destinationFolder via step 3, use moveFile method to move it to that location (docs)

    fileObject.moveFile(destinationFolder)

    Warning:
    Have not tested the code, but this is what I could from the documentation.

    Login or Signup to reply.
  2. 0

    SUGGESTION:

    I’ll probably tackle your first issue where you had issues with granting access to their files once the email was sent. You can use this line to grant them access automatically on your script.

    newSpreadsheet.addEditor(emailAddress);

    So the modification would be from:

      if (emailAddress) {
    var subject = "Your new G sheet is ready";
    var body = "Dear" + name + ",nnYour newsheet is attached.nnHere is the link:" + newSpreadsheet.getUrl();
    MailApp.sendEmail(emailAddress, subject, body);
  }

    To:

      if (emailAddress) {
    newSpreadsheet.addEditor(emailAddress);
    var subject = "Your new G sheet is ready";
    var body = "Dear" + name + ",nnYour newsheet is attached.nnHere is the link:" + newSpreadsheet.getUrl();
    MailApp.sendEmail(emailAddress, subject, body);
  }

    Reference:

    https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#addeditoremailaddress

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search