UKBouldering.com

Google Gsheet Scripts (Read 1398 times)

SamT

Offline
  • *****
  • forum hero
  • Posts: 1830
  • Karma: +87/-0
Google Gsheet Scripts
September 10, 2020, 09:41:29 am

Anyone any knowledge of google gsheet scripts.

Long story short - have a very large gsheet that forms our work schedule at work. Would like to be able to run a script that sends me an email for every row where certain conditions met namely date from 6 months ago, Y in a certain column. (and probably some other conditions) 

Ideally the script would run automatically every day against the Gsheet.

I'm not really that up on scripts/java etc but slowly muddling through.  Found the 'sendEmails function and initial googling seems to suggest that I need to be looking at "Project Triggers"

Any advice/pointers gratefully received.

remus

Online
  • *****
  • forum hero
  • Posts: 2145
  • Karma: +89/-1
#1 Re: Google Gsheet Scripts
September 10, 2020, 10:07:59 am
That sounds pretty doable.

You're correct that what you're after is a trigger. Broadly this looks like what you want https://developers.google.com/apps-script/guides/triggers/installable and this looks like a useful example https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_programmatically

For iterating over the rows in your spreadsheet these look like what you're after

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastColumn()
https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()

to get the extents of the data, then

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)

for selecting the relevant range of data. There's a handy looking example in that last link for iterating over the data. You'll want to do something very similar, it'll probably look a bit like this

Quote
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, 3, 3);
var values = range.getValues();

// send email if conditions are met
for (var row in values) {
  if (values[row][col_with_ys] === 'Y' and values[row][date_col] === something) {
    sendEmail();
  }
}

SamT

Offline
  • *****
  • forum hero
  • Posts: 1830
  • Karma: +87/-0
#2 Re: Google Gsheet Scripts
September 10, 2020, 11:45:34 am

 :bow:

Nice one.  Will have a look later and see how I get on.

SamT

Offline
  • *****
  • forum hero
  • Posts: 1830
  • Karma: +87/-0
#3 Re: Google Gsheet Scripts
November 29, 2020, 04:54:00 pm
Hi Remus if you're out there.

took a break from this but just come back to it today.

Managed to get it working so it sends me an email when there is a Y in a certain column,  But struggling to add an extra condition so that its  (in english)

if column x = 'Y'
and
date in column A is 6 months ago

Again, any help (from anyone) is gratefully received.

remus

Online
  • *****
  • forum hero
  • Posts: 2145
  • Karma: +89/-1
#4 Re: Google Gsheet Scripts
November 29, 2020, 08:24:41 pm
If the data is not too sensitive you can always share the sheet + script with me and I'll have a look (PMd you my email address), otherwise would you be able to copy and paste a little sample of what you have so far?

SamT

Offline
  • *****
  • forum hero
  • Posts: 1830
  • Karma: +87/-0
#5 Re: Google Gsheet Scripts
November 29, 2020, 10:35:47 pm
Hi Remus.

Apologies, only just seen this. ..  cracked it myself  ;D

Might be a bit long winded but I did this based on various stuff found online.

First bit of coding I've done in over 10 years. and its not Cobol!  Quite chuffed.  :smartass: :smartass:
(taken me all day mind!!)

Quote

//Sends an Email to info@etc, for each row where there is a Y in the AT column, and the date is either 6 or 12 months ago


function emailAlert() {
  // today's date information
  var today = new Date();
  var todayMonth = today.getMonth() + 1;
  var todayDay = today.getDate();
  var todayYear = today.getFullYear();
 
  // 6 months ago
  var sixMonthsAgo = new Date();
  sixMonthsAgo.setDate(sixMonthsAgo.getDate() - 182);
  var sixMonthsMonth = sixMonthsAgo.getMonth() + 1;
  var sixMonthsDay = sixMonthsAgo.getDate();
  var sixMonthsYear = sixMonthsAgo.getFullYear();
 
  // 1 year ago
  var oneYearAgo = new Date();
  oneYearAgo.setDate(oneYearAgo.getDate() - 365);
  var oneYearMonth = oneYearAgo.getMonth() + 1;
  var oneYearDay = oneYearAgo.getDate();
  var oneYearYear = oneYearAgo.getFullYear();

 
  // getting data from spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 8000; // Number of rows to process

  var dataRange = sheet.getRange(startRow, 1, numRows, 20);
  var data = dataRange.getValues();

  //loop through all of the rows
  for (var i = 0; i < data.length; ++i) {
    var row = data;
 
    // email information
    var subject = '';
    var message =
      ' Reminder - ' +
      '\n' +
      ' Name: ' +
      row[1] +
      '\n' +
      ' Job: ' +
      row[2];

    //trigger date information
    var rowDateMonth = new Date(row[0]).getMonth() + 1;
    var rowDateDay = new Date(row[0]).getDate();
    var rowDateYear = new Date(row[0]).getFullYear();


    //Checking for 6 Months from now
    if (
      rowDateMonth === sixMonthsMonth &&
      rowDateDay === sixMonthsDay &&
      rowDateYear === sixMonthsYear &&
      row[10] === 'Y'
    ) {
      var subject =
        'Reminder 6 month: ' +
        row[1] +
        ' - ' +
        row[2];
      MailApp.sendEmail('info@etc', subject, message);
    }

    //Checking for 1 year from now
    if (
      rowDateMonth === oneYearMonth &&
      rowDateDay === oneYearDay &&
      rowDateYear === oneYearYear &&
      row[10] === 'Y'
    ) {
      var subject =
        'Reminder 1 Year: ' +
        row[1] +
        ' - ' +
        row[2];
      MailApp.sendEmail('info@etc', subject, message);
    }
  }
}

remus

Online
  • *****
  • forum hero
  • Posts: 2145
  • Karma: +89/-1
#6 Re: Google Gsheet Scripts
November 29, 2020, 10:49:18 pm
 :weakbench: Nice one Sam!

One little thing to be aware of with your script above is that the 6 months ago calc might produce some slightly unintuitive results depending on exactly what you're after. The problem is months having variable numbers of days which makes calculating '6 months ago' fiddly. Your days solution is good as it'll give you consistent answers, but if someone were to work it out by hand they might give you different answers in some cases.

SamT

Offline
  • *****
  • forum hero
  • Posts: 1830
  • Karma: +87/-0
#7 Re: Google Gsheet Scripts
November 30, 2020, 12:57:58 pm

Yeah - I have thought that.  30days in some months, 31 in others etc, also leap years etc.  Its not super critical if the odd day is missed.

 

SimplePortal 2.3.7 © 2008-2022, SimplePortal