Tuesday, 25 March 2014

Financial Year Formulae in Google Sheets

A lot of my reports involve breaking down data into financial years, April to March.
Switching to Google sheets from excel has meant that I have had to find a new formulas and methods to do this, which are documented here.

Simple method...

Assuming your date is in cell A3 the following formula will work fine

=IF(MONTH(A3)<4, YEAR(A3)-1 &"/"&Right(YEAR(A3),2), YEAR(A3) &"/"&right(YEAR(A3)+1,2))

end result would be shown as for example  

DateFinancial Year

More powerful method...

This is all well and good - but Google sheets have a fantastic ability to use an array, entering in a single formula in a cell and the sheet then automatically calculating for the whole column.

=ArrayFormula(IF(MONTH(A3:A)<4, YEAR(A3:A)-1 &"/"&Right(YEAR(A3:A),2), YEAR(A3:A) &"/"&right(YEAR(A3:A)+1,2)))

End result

DateFinancial Year

Even better method...

As you can see from the example above, the array fills down all cells, giving a load of unwanted results where there is no date in column A.
The solution is to include another IF, which instructs only to fill down those cells where there is data in column A.

=ArrayFormula(IF(len(A3:A),IF(MONTH(A3:A)<4, YEAR(A3:A)-1 &"/"&Right(YEAR(A3:A),2), YEAR(A3:A) &"/"&right(YEAR(A3:A)+1,2)),iferror(1/0)))

DateFinancial Year

Hope this is of use, especially to my NIHR colleagues!

Thursday, 6 March 2014

Recreate SharePoint lists in Google Apps, with both bulk editing and edit form functionality

This solution came about from a requirement to recreate SharePoint lists in Google Apps, taking across large numbers of list items, with each item needing to have  its own edit form populated with the appropriate data. Additionally, users should be able to edit the Google Sheet, and have those changes reflected in the edit forms. This attempts to give the same functionality as SharePoint where items can be edited in edit form, or in bulk via datasheet view.

The solution involves a Google Sheet, with related form, some scripting, and an Awesome Table to display the end result.

To begin, I created a form, with the same field names and types as on SharePoint, and a Google Sheet to collect these responses.

Next I used a great script used as part of an Awesome Table gadget
This uses the forms service, to find the appropriate URL for each submitted form, and write this to the sheet. (in my example, this is writing the URL to column E (5))

var formURL = 'https://docs.google.com/a/nihr.ac.uk/forms/d/abcdefghijklmnop123456789/edit';
var sheetName = 'Master';
var columnIndex = 5;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if(data[i][0] != '' && (data[i][columnIndex-1] == '' || !data[i][columnIndex-1])) {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl); 

I set a trigger for this to run on form submit, so that when new forms are created, the URLs can be obtained and recorded in the sheet

I could have pasted in the historical data list from SharePoint at this point, but these would not have an edit form associated with them.  I therefore used a script to generate a number of blank form entries, which could be later used as the edit forms for the data.

function CreateSomeBlanks() {
  var form = FormApp.openById('abcdefghijklmnop1234567890');  //replace with your own form ID
  var numberrows = Browser.inputBox('How many blank forms do you want to create', Browser.Buttons.OK_CANCEL);
  for (var x = 0; x < numberrows; x++) {
    var formResponse = form.createResponse();
    Utilities.sleep(1000);  // we want each entry to have a unique timestamp, so introduce a delay

With Google forms you can use a "get prefilled url" so that for new forms specific data can be entered already when the user loads the new form up. I found that you could use this method also for edit forms and use the awesome table to specify what data needs to go in. This allowed me to adapt the generated URL to prefill the edit forms with sheet data.

(NB Any dates need converting into "yyyy-mm-dd" as text using an array formula such as =ArrayFormula(text(K3:K,"yyyy-mm-dd")) as otherwise the prefil does not work correctly)

Once this is done, paste in all the historical data into the Google Sheet.

Here's the Awesome Table setup used for the demo
And here's the background sheet

The end result - each row has an edit link, while any changes to the edit forms write back to the sheet. In addition if any changes are made to the sheet, these values are also populated in the edit forms.