Generating an emailed roundup of outstanding actions

I recently was asked to generate an emailed reminder for actions following meetings.  This solution was put into place to do this, but rather than send out an email reminder per action, this combines all outstanding actions for each user and sends them a single email, with a link to update each item.

The end result looks something like this:

This was done in part by using a useful formula in Google I had been unaware of until recently, "REPT"  Combined with CONCATENATE, this can pull through matching items from another range of cells.

e.g. in my demo sheet  the formula =ArrayFormula(concatenate(rept(Outstanding!L:L&" ",Outstanding!C:C=A3)))
brings in all items from column L in one sheet, where Column C in that sheet matches cell A3.

In the demo sheet, this formula brings through some concatenated HTML table code which wraps the data for each line.

Progress stars correspond to a Google form Linear Scale question  (1 to 5) with a simple formula to select the appropriate image.

So that only those with actions get sent emails, I used a query to just bring in items with less than 5 stars (complete) and then a pivot query on those items.

A copy of a demo sheet including scripts can be found here

For the scripts -  I just set up the getEditResponseUrls script to run on Form submit (change the Form URL in the script to yours before doing this)  and the sendweeklyemail script to run on a timer.

Another tweak that would need to be made would be to amend the form to put in the list of names for the actions, replacing the ones in there, and putting in the matching names and email addresses in the Vlookup tab in the Google Sheet.


The demo sheet is also set up to display easily in an Awesome Table as per the example below

Hope this is of interest, any questions please let me know

Using Awesome Tables with Google Form Attachments

Google recently added an attachment upload function to Google GSuite customers.  This functionality has replaced some workarounds, including one I have blooged about before using Folders

Google creates a Drive folder for the attachments, and lists the attachments URLs in the response sheet, separated by commas.  This is great, but to display the information in a Google site or similar, it is most useful to use an Awesome Table to display the results.  To get this formatted to suit an Awesome Table is a bit fiddly however.   This post explains a way to get this set up

First - here's the demo of the completed table

_____________________________________________________________________________

_____________________________________________________________________________

To begin with I created a new tab which is the one that the Awesome table will read from, manually inputted the column names in the first row, added filters on the second, and in the third row added in a query to bring the data in from the form. 

As the file URLs are all in one column, separated by a comma and a space, it would not be possible to hyperlink these with a formula - the files need to be split out initially - this is done with an array formula with a split function, using ", " as the separator

Next a Google Apps script was added to list the URLs and names of the folder that was created automatically to contain the form attachments.  The code should be set on a trigger, on form submit.
The script formats the URL to match the same syntax that the Google form produces and will output to a tab I created called 'FileList'.

function search()  {
 
    var folder =  DriveApp.getFolderById("***INSERT YOUR GOOGLE DRIVE FOLDER ID HERE**");
     
  var files = folder.getFiles();
   
    
     var sheetName = 'FileList';  
      
var SheetIds = 2;
 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
 
  sheet.clear();
 
  sheet.appendRow(["URL","FileName"]);
 
 while (files.hasNext()) {
 
   var string = 'https://drive.google.com/open?id=';
   var file = files.next();
   Logger.log(file);
   Logger.log(string);
 var data;
    data = [
      string + file.getId(),
     file.getName(),
      

    ];
 
    sheet.appendRow(data);
 
  }
  SpreadsheetApp.flush();

  
};

End Result is as per the below screenshot when it runs

Now we have the URL and the Name, vlookups can be added to each of the 'Doc' columns to bring through the filenames

With all this done, the final step is to add a tab called template, which can then be used in the Awesome table setup.   Screenshot below, but the file is linked at the end of this post, in case you want to copy and paste.  The documents column takes the URLs and file names and hyperlinks each one with a generic document icon

In the Awesome Table setup - just reference the template to apply this

A copy of the sheet is here: https://docs.google.com/spreadsheets/d/1Y2bvKyA71GEAjf0RmIJOgADZyOHuAjGH5EzgRKfL8RU/edit#gid=1206792601

Hope this is of use!

Marking assessments and generating certificates automatically via Google sheets, app script, forms, slides and Form Publisher

We recently had a requirement for users to complete a short assessment, to record which users passed, and for those who did, to receive a certificate.
To do this I used Google Apps script, Google Apps and a great add-on, Form Publisher

Here's a diagram of how it all works...

In the background collection sheet of the assessment quiz, formulae work out whether the answers are correct and score them a 1 or a 0 -  A formula at the end assigns either a 1 (pass) or a 0 (fail) based on the target score. Those who have achieved a pass mark, have their details pulled through into another tab, using a simple query.  e.g

=query('Form responses 1'!Y2:AE,"select Z,AA WHERE (Y=1)")

A similar tab can be created to pull through those who have failed just using Where =0 instead

How to then pass the details into a certificate is achieved using Google Apps Script.  This script populates a new form, which uses the Form Publisher add-on and submits it.

var sheetName = 'Mail Merge'; // enter the name of the sheet

function GenerateCertificate() {
  
  var CERTIFICATE_CREATED = "CERTIFICATE_CREATED";  //Define flag used to stop duplicates
  
  var form = FormApp.openById('abcdefghijklmnopq123456789');  //your form id goes here
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
   var data = sheet.getDataRange()  // Get all non-blank cells
   .getValues()     // Get array of values

      var startRow =1;  // First row of data to process 
    
   for (var i=0; i<data.length; i++){
     
    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = data[i];
         var processedstatus = row[4];  // column - Column containing the flag to stop duplciates  (A=0)
     
      if (processedstatus != CERTIFICATE_CREATED) {     //If Flag text not found then.....
     
        //Take the first column data and input this into the next form  (e.g Full Name)
     var items = form.getItems(FormApp.ItemType.TEXT)
     var formItem = items[0].asTextItem();
    var response = formItem.createResponse(row[0]);     
    formResponse.withItemResponse(response);

         //Take the second column data and input this into the next form (e.g. Name of assessment)
    var formItem2 = items[1].asTextItem();  
    var response = formItem2.createResponse(row[1]);     
    formResponse.withItemResponse(response);

        //Take the third column data and input this into the next form  (e.g. date completed)
    var formItem3 = items[2].asTextItem();
    var response = formItem3.createResponse(row[2]);     
    formResponse.withItemResponse(response);

        //submit the form
    formResponse.submit();
    Utilities.sleep(500);
    
        //set the flag to stop duplicates
      sheet.getRange(startRow + i, 5).setValue(CERTIFICATE_CREATED);
      
       SpreadsheetApp.flush();
      }

  }

};

The script will only process those rows without the flag "CERTIFICATE_CREATED" in column E, once it has processed that row, it adds that text, to prevent duplicates being created.

In the background, when the script populates the user name, assessment and date into the next form and submits it.  Form Publisher merges the fields into a certificate template I set up in Google Slides, saves the certificate as a pdf, and then emails it to the person who took the test.

A useful feature of using a second form, processed by a script, is that sheets for different assessments / courses can use that same template and same 'certificate generator' form to create appropriate certificates.  All that is needed is the script in a background collector sheet to populate that form with the required data.