Friday, 25 April 2014

Create and share customised templates linked to a master collection sheet

There are lots of times when it would be useful to create batches of sheets with customised information, share these with specified people automatically, and have any changes that those users make, automatically collected and visible from one master sheet.

I have created a variant of this script to collect a large volume of information from 200+ organisations, but am going to demo a simpler version here, which aims to create individual annual leave sheets for all staff, populating each one with data relevant to each person, sharing these with them individually, and allowing the main administrator to see at a glance what the remaining balances of holidays are for everyone as time goes on and the staff update their sheets.

To start, I created a simple sheet - shown here with some sample data:

The Spreadsheet IDs and Email Sent cells will get populated automatically once the script runs.
The Current Balances and Link to Spreadsheets are generated using formulae (importrange and a hyperlink concatenation respectively)

I also created a separate template sheet workbook (below), which the script will customise using the information on the master sheet above, make a copy, then share with the appropriate staff members.

For the Scripts, I created a couple of menu entries that will appear when the master sheet loads

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{
    name: "Generate Annual Leave Sheets",
    functionName: "CreateAnnualLeaveSheets"
    name: "Send Emails",
    functionName: "sendEmails"
  ss.addMenu("Run Annual Leave Scripts", menuEntries);

Next I created the main script, which will create a customised copy of each template and share it with the user whose email address is in column B

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Master'; //I renamed Sheet1! to Master
var SheetIds = 3;  //this is the column to store the generated sheet IDs

function CreateAnnualLeaveSheets(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  //use the id of the template sheet
  var sstemplate = SpreadsheetApp.openById("**************************"); //replace with your template key
  var data = sheet.getDataRange().getValues();
  for(var i = 1; i < data.length; i++){
    if(data[i][0] != '' && (data[i][SheetIds-1] == '' || !data[i][SheetIds-1])) {
     var reportname = data[i][0];  // this is column A (A=0, B=1, C=2 etc.)
     var AdditionalEditor = data[i][1];  //column B  - the email that template will be shared with
     var Name = data[i][5];  // column F   - their name
      var Department = data[i][6];  //column G  - their department
      var Basic = data[i][7];  //column H   - number of days annual leave
      var cfwd = data[i][8];  //column I   - number of days carried forward
       sstemplate.getSheetByName("Sheet1").getRange('B2').setValue(Name);   // append name to the template
       sstemplate.getSheetByName("Sheet1").getRange('B3').setValue(Department); // append department 
        sstemplate.getSheetByName("Sheet1").getRange('B4').setValue(Basic);  // append no. days basic
         sstemplate.getSheetByName("Sheet1").getRange('B5').setValue(cfwd);  //append no days carried forward
      var newsheet = sstemplate.copy(reportname).getId();  //create a copy of the template and save it with the report name
           sheet.getRange(i+1, SheetIds).setValue(newsheet);  // write the newly created key to the master sheet
        var newsheeteditors = SpreadsheetApp.openById(newsheet);  // open each sheet
        newsheeteditors.addEditors([AdditionalEditor]);   // assign edit rights to the named person
  } //loop

When this script is run, the spreadsheet keys all fill in automatically on the master sheet. This allows us also to reference each of the keys in a formula, to bring in selected data to the master.  In this case I just wanted to see the outstanding holiday balance for each staff member.  For this I just needed a formula such as:


This formula allows cell G2 from each of the staff sheets to be brought through to the master sheet.
Whereas old sheets limited you to 50 Importrange formulae, new sheets has no limit, but it does require you to authorise each data connection. You only have to do this once for each of the linked sheets, so you can have multiple importranges to the same child sheet, which will all work as soon as just one of them is authorised.

I just used conditional formatting for the cell to go red if there is an error (such as if the sheets are not yet connected) Once you have clicked on "Allow Access"  the data should come through from then on.

I also have an email script that is designed to contact each of the staff members, informing them that a sheet has been created in their 'Shared with me' folder, with a link direct to the sheet for convenience.

//allow specific text to be written to sheet to prevent duplicate emails being sent

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 21;   // Number of rows to process
  // Fetch the range 
  var dataRange = sheet.getRange(startRow, 1, numRows, 12)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[1];  // seccond column  (Column B)
    var message = row[10];       // column K
    var emailSent = row[9];     // column J  
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = row[0];     //  column (A)
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 10).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted

function SheetFlush(worksheet) {
    worksheet = worksheet || SpreadsheetApp.getActive();
    var sheets = worksheet.getSheets();

You can customise the body and title using text and formulae on the master sheet, on mine, the end result looks something like this.

Variations of this script can be used to send regular customised reports out to large numbers of users, or to send complex data collection sheets that may need to be worked on over longer periods of time.

Hope it is of use, any questions or feedback welcomed

Tuesday, 8 April 2014

Linking Google Sheet data to MS Excel and MS Access

With SharePoint I used to link lists to both Excel and Access quite frequently.  Although Google Sheets does not have the two way interactivity that has been so useful, it is still possible to have data that is entered on a Google Sheet, to be automatically imported and refreshed to a local Excel workbook.  In addition, its possible to use Excel as a bridge to then get Access to use that data.

Link a Google Sheet to Excel

To bring in Google Sheet data to Excel, you need to do a web query in Excel

Enter the URL of your Google Sheet and tick the green box to the left to select the sheet data

This return you something like this in Excel

Trust Start_Date End_Date Recruits п»ї п»ї
Trust A 08/04/2014 29/03/2014 50 п»ї п»ї
Trust B 17/02/2014 07/02/2014 20 п»ї п»ї
Trust C 19/12/2013 09/12/2013 30 п»ї п»ї
Trust B 20/10/2013 10/10/2013 40 п»ї п»ї
Trust D 08/04/2014 08/04/2014 10 п»ї п»ї
Trust E 07/02/2014 08/04/2014 10 п»ї п»ї
Trust F 09/12/2013 07/02/2014 1 п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї
п»ї п»ї п»ї п»ї п»ї п»ї

As you can see, for any blank cells, these produce "п»ї"  symbols.   You can either hide these with conditional formatting in Excel, or to be much cleaner, just delete empty rows and columns in Google Sheets. If you need to add new data in the Google sheet at any time, its easy just to create new rows as required.

After deleting the unused rows and columns in Google sheets, it now looks like this

In Google Sheets

In Excel

The data in Excel can be refreshed manually by right clicking on the data and clicking refresh, or if you want this to be automatic whenever the sheet is opened, you can right click in the data, and click "Data Range Properties"  clicking the box "refresh data when opening the file"

This should now give you a live link to the Google Sheet data.

Synchronising with MS Access

In Access, you can link an Excel workbook as an Access Table, allowing the data to be then used in queries.

To link in your Excel workbook which contains the linked data from Google Sheets, open Access, and Import and Link as per the below screenshot

Choose the following radio button option

When you have completed the import, you should now see your Google Sheet data, as a table.

As you can see from the above example, Access has recognised numerical and date fields, and my headers for these rows come across as errors.   If you have the same issue, this can easily be sorted in a simple query based from this data as in the below:

Design view:

Datasheet view:

To refresh data from Google sheets therefore, you would just need to open your Excel workbook, allow the refresh from Google to complete, and then open your Access Database, and pull in the new data automatically.  If you wanted, with a few lines of VB you could also get Access to open the Excel workbook when it opens and before it runs queries on the new data.

Please let me know your feedback on this, especially if you encounter any bugs!

Friday, 4 April 2014

Using a Vlookup in an array

Vlookup must be the most common formula I use in Excel - In the past I have had to use some VB script to ensure that these formulas fill down to the correct row, while if I want to bring in multiple columns I have had to enter multiple formulas in the appropriate rows.

With Google Sheets, I have found that its possible to use a single formula, which can bring in multiple columns of data in adjacent rows, which also has the advantage of being dynamic - if new data is added, the formula returns the matching data for the new row automatically.

Here is a sample working formula -


The way that this formula works, is that it looks for data in column A, and for each entry, returns matching data from columns B,E and F in the source sheet named "DATA" (B,E,F are 2,5,6 in the above code)

In the example below - the formula has been put in cell B2 and the lookups all complete....

Hope this is of use