@bytesnz

Jack Farley, Web Application Engineer

Google Sheets Fun

Programming

So I am currently having to tinker with Google Sheets for some volunteer work I am doing. The organisation is currently using Google Sheets for management of volunteers etc (argh!). As an interim measure (will move to a database) to help make the sheets a little easier to use, I have created some scripts to do some fancy tricks.

There are a few very cool things about Google Docs (not mentioning trying to use Docs over a bad link), one of which is Google Script (documentation is available here). The scripts are written in Javascript and can be run either at certain times or on certain events, including onEdit and onOpen.

Below are the functions that I created.

This is a function to send out email alerts if a date on a row is 7, 3 days away, the current day, or the date has passe. It will email the alert to email addresses specified in a cell range.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
function reminderAlert() {
  // Get Volunteer form and sheet
  var spreadsheet = SpreadsheetApp.openById('xxxxx');
  var sheet = spreadsheet.getSheetByName('Volunteer Summary'); 
  var recipients = removeBlanks(spreadsheet.getRangeByName('alert_emails_followup').getValues());
  var followupDates = spreadsheet.getRangeByName('volunteer_followup_date').getValues();
  var names = spreadsheet.getRangeByName('volunteer_name').getValues();
  var emails = spreadsheet.getRangeByName('volunteer_email').getValues();
  Logger.log('Checking Followup dates');
  // Go through (skipping the title row)
  for(var i = 1; i < names.length; i++) {
    if (names[i] != '') {
      if (followupDates[i] != '') {
        var when;
        var today = new Date().valueOf();
        var due = new Date(followupDates[i].toString()).valueOf();
        var count = due - today;
        count = Math.floor(count / 1000 / 60 / 60 / 24);
        if (count == 7 || count == 3 || count == 0 || count < 0) {
          if (count < 0) {
            when = 'was due ' + (count * -1) + ' days ago';
          } else if (count == 0) {
            when = 'is due today';
          } else {
            when = 'is due in ' + count + ' days';
          }
          var subject = 'NGO Taxi: Follow up with ' + names[i] + ' ' + when;
          var message = 'Hello,\n\nThe follow up with ' + names[i] + ' ' + when + '.\n' +
            'Their details are:\n' +
              'Name: ' + names[i] + '\n' +
                'Email: ' + emails[i] + '\n' +
                  'Comments:\n' + getComments(names[i], 5) + '\n\n' +
                  'For details, see: ' + spreadsheet.getUrl() + '\n' +
                    '[This is an automated message, do not reply.]';
          Logger.log('Sending email about ' + names[i]);
          MailApp.sendEmail(recipients, subject, message, { noReply: true });
        }
      }
    }
  }
}

This function gets all the comments (stored in a comments sheet) for a particular volunteer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
function getComments(name, limit) {
  var c, comment = {}, commentDates = [];
  var spreadsheet = SpreadsheetApp.openById('xxxxx');
  var dates = spreadsheet.getRangeByName('volunteer_comment_dates').getValues();
  var names = spreadsheet.getRangeByName('volunteer_comment_names').getValues();
  var comments = spreadsheet.getRangeByName('volunteer_comments').getValues();
  for(var i = 1; i < names.length; i++) {
    if (names[i] == name) {
      var date = new Date(dates[i]).valueOf();
      comment[date] = (dates[i] + ': ' + comments[i]);
      commentDates.push(date);
    }
  }
  if (commentDates) {
    commentDates.sort(function (a, b) {
      if (a > b)
        return -1;
      if (a < b)
        return 1;
      // a must be equal to b
      return 0;
    });
    if (!limit) {
      limit = commentDates.length;
    }
    var commentParts = [];
    for (c = 0; c < limit; c++) {
      commentParts.push(comment[commentDates[c]]);
    }
    return commentParts.join('\n');
  } else {
    return '';
  }
}

This is a function to automatically insert a date into a cell of the active row if the row is modified. The date will be deleted if the rest of the row is cleared. You can specify what sheets/columns dates will be added to.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
function insertDate(sheet) {
  var dates = [
    {
       sheet: 'Comments', // Name of the sheet
       column: [2, 3], // Columns to check for content
       minRow: 4, // The start row (to ignore headings
       dateColumn: 1 // The column that the date should be inserted into
    },
    {
      sheet: 'Volunteer Summary',
      column: 6,
      minRow: 2,
      dateColumn: 7,
      baseDateColumn: 6, // Instead of adding the current date, use the value in this column
      addDays: 4, // Add this many days to the date
      overwrite: true, // Overwrite any values that are in there
      overwriteIfNewer: true // Only overwrite if is a newer date
    }
  ];
  var d, aCell, dCell, c;
  for (d in dates) {
    if (sheet.getName() == dates[d]['sheet']) {
      aCell = sheet.getActiveCell();
      var col = dates[d]['column'];
      if (col instanceof Array) {
      } else {
        col = [col];
      }
      for (c in col) {
        Logger.log('checking if col ' + col[c] + '(' + aCell.getColumn() + ')');
        Logger.log(dates[d]['minRow'] + ' >= ' + aCell.getRow());
        Logger.log('Offset will be ' + (col[c] - aCell.getColumn()));
        if (aCell.getColumn() == col[c]
            && (dates[d]['minRow'] ? (aCell.getRow() >= dates[d]['minRow']) : true)) {
          var value;
          // Check for and set value based on baseDate column
          if (dates[d]['baseDateColumn']) {
            var bDCell = aCell.offset(0, (dates[d]['baseDateColumn'] - aCell.getColumn()));
            value = new Date(bDCell.getValue());
            if (value && dates[d]['addDays']) {
              value.setDate(value.getDate() + dates[d]['addDays']);
            }
          }
          // Set value to current date if we don't have one
          if (!value) {
            value = new Date();
          }
          // Current cell to change
          var dCell = aCell.offset(0, (dates[d]['dateColumn'] - aCell.getColumn()));
              Logger.log('Current value is :' + dCell.getValue());
          if (aCell.getValue() != '' && (dates[d]['overwrite'] || dCell.getValue() == '')) {
            Logger.log('setting value');
            dCell.setValue(value);
            break;
          } else if (aCell.getValue() == '' && dCell.getValue() != '') {
            Logger.log('setting value');
            dCell.setValue('');
            break;
          }
        }
      }
    }
  }
}

This function hides rows of a sheet based on the value of a column in the row. When the value is changed, the function will hide the rows that aren’t associated with that value (the value is in a specified column. It will also hide column that it looks for the value in. When a new row is started, the selected value will automatically be inserted into the specified column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
function valueSpecificSheet(sheetObj) {
  var sheets = [
    {
      sheet: 'Comments', // The name of the sheet to monitor
      valueCell: [3, 2], // The cell to get the value of [col, row]
      column: 2, // The column number to look for the value (and to hide etc)
      startRow: 4,
      lastColumn: 3,
    },
    {
      sheet: 'Activity Tracker', // The name of the sheet to monitor
      valueCell: [7, 2], // The cell to get the value of [col, row]
      column: 1, // The column number to look for the value (and to hide etc)
      startRow: 4,
      lastColumn: 8,
    },
  ];
  var d, aCell, dCell, c, sheet, value;
  for (s in sheets) {
    sheet = sheets[s];
    if (sheetObj.getName() == sheet.sheet) {
      var lastRow = (sheet.lastRow ? sheet.lastRow : sheetObj.getLastRow());
      aCell = sheetObj.getActiveCell();
      var selectedValue = sheetObj.getRange(sheet.valueCell[1], sheet.valueCell[0]).getValue();
      if (aCell.getRow() == sheet.valueCell[1] && aCell.getColumn() == sheet.valueCell[0]) {
        // Check if the edited cell is the value Cell, if not check for new row
        // Unhide everything
        sheetObj.showRows(1, lastRow);
        if (selectedValue) {
          sheetObj.hideColumns(sheet.column);
          // Go through rows and hide the rows with a values not equal to selected value
          for (i = sheet['startRow']; i <= lastRow; i++) {
            // Check value
            value = sheetObj.getRange(i, sheet.column).getValue()
            if (value != '') { // Ignore empty row
            } else if (selectedValue != value) { // Hide
              sheetObj.hideRows(i)
            } 
          }
        } else {
          sheetObj.showColumns(sheet.column);
        }
      } else if (selectedValue != '') {
        // Check if there are values in the row, if so make sure the value in column is correct
        var lastColumn = (sheet.lastColumn ? sheet.lastColumn : sheetObj.getLastColumn());
        var dCell = sheetObj.getRange(aCell.getRow(), sheet.column);
        var haveValue = false;
        for (i = 1; i <= lastColumn; i++) {
          if (i == sheet.column) {
            continue;
          }
          if (sheetObj.getRange(aCell.getRow(), i).getValue() != '') {
            haveValue = true;
            break;
          }
        }
        if (haveValue) {
          if (dCell.getValue() != selectedValue) {
            dCell.setValue(selectedValue);
          }
        } else {
          if (dCell.getValue() != '') {
            dCell.setValue('');
          }
        }
      }
    }
  }
}

This is just a simple function to remove blank rows from a array of rows.

1
2
3
4
5
6
7
8
9
function removeBlanks(values) {
  var v = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i] != '') {
      v.push(values[i]);
    }
  }
  return v;
}