CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsdrive
hakarune
hakarune

Reputation: 412

Checking old data against new and only processing actual changed values in google app scripts on sheets

I have a sheet that scrapes information from another sheet and organizes it into 3 sheets (70%, 80%, 90%). My current onChange script works, but fires any time there is any change to any cell and not just if the percentage for a client changes; currently I'm getting a lot of repeated emails with the same information. Is there a way to store the previous row values and check against them and only process what's new?

Here's what I'm trying, but the script debugger keeps coming back with TypeError: Cannot read property "1" from undefined. (line 12, file "Code"). I know it's because oldData is currently empty, and I'm stuck on what to do to make it work. If anyone could help or at least point me in the direction of a better way would be a great help.

function notification70() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0]; //”0″ is the first sheet
    var startRow = 2; // First row of data to process-actual row# (this is cell A1)
    var numRows = 10; // Number of rows to process (goes from A1-A10)
    var dataRange = sheet.getRange(startRow, 1, numRows, 3) //set range
    var formats = dataRange.getNumberFormats(); //get the format style of the cells
    dataRange.setNumberFormats(formats); //set the format of the array to the same as the data (currently not working)
    var data = dataRange.getValues(); // Fetch values for each row in the Range.
    for (i in data) {
        var row = data[i]; //convert data to var to row for easier memory on what I'm writing
        if (row[i] = oldData[i]) continue; //if current data percentage = the previous then skip
        var emailAddress = "[email protected]"; // Email
        var percent = row[1]*100; //convert to % value
        var percentage = parseFloat(percent).toFixed(2); //cut to 2 decimal points
        var message = "You have now reached 70% of your time for " + row[0] + ". You are currently at " + percentage + "% for " + row[0] + ". Keep up the good work."; // emails the information for the row schanged
        var subject = "You have reached 70% of the time for " + row[0] + ". #Notification"; //email subject

        if (percent != 0) Logger.log(emailAddress + "\n" + subject + "\n" + message); //log the output for testing

    }
   var oldData = data //update/save current values for next script run
}

The sheet data looks like this and will :

      Client   |     Percentage   |        Date
   ---------------------------------------------------
      BBB      |      78.19%      |      1/18/2016
   ---------------------------------------------------
      DDD      |      79.63%      |      1/18/2016
   ---------------------------------------------------
      FFF      |      74.54%      |      1/18/2016
   ---------------------------------------------------
      KKK      |      72.30%      |      1/18/2016
   ---------------------------------------------------
      MMM      |      71.47%      |      1/18/2016
   ---------------------------------------------------
      PPP      |      73.39%      |      1/18/2016

Thank you all for the help ^_^

Upvotes: 1

Views: 574

Answers (2)

Alan Wells
Alan Wells

Reputation: 31300

Put the "old data" into the document properties. Either that, or create a new sheet tab for the "old data". It looks like you aren't getting that much data, so it might fit into a property of the document properties.

function storeRowsOfData(data) {

  var objOldData = {};
  var keyName = "",
      thisRowArray;

  for (var i=0;i<data.length;i+=1) {
    keyName = "row" + (i+2).toString();
    thisRowArray = data[i].toString();

    objOldData[keyName] = thisRowArray;
  };

  PropertiesService.getDocumentProperties().setProperties(objOldData, true); //true deletes all other properties
};

If you don't delete all other properties, and the length of the rows on the current action were less than the last, you would have left over rows from the old, old data. If you are not using document properties for anything else, and don't have an Add-on that uses document properties, then you'll be okay.

Code:

function getRowsOfOldData() {
  var oldValues = PropertiesService.getDocumentProperties().getProperties();

  var outerArrayOldData = [];
  var thisLoopString,
      thisRowArray;

  for (var key in oldValues) {
    thisLoopString = oldValues[key];
    thisRowArray = []; //Reset
    thisRowArray = thisLoopString.split(","); //Convert the string to an array

    outerArrayOldData.push(thisRowArray);//Push the inner array into the outer array
  };

  Logger.log('outerArrayOldData: ' + outerArrayOldData);
};

The function to get the data, puts all the data back into a two dimensional array. That's it's original form. The function to put the data into the document properties, did not put all the data into one property, but into multiple properties. When something is put into document properties, it is converted to a string. You can not store data as an array or object in document properties. And, putting each row into it's own property avoids any issues with data limitations per property setting.

Upvotes: 2

Ri&#235;l
Ri&#235;l

Reputation: 1321

For the error: add var oldData = []; before the for loop to define it.

Upvotes: 0

Related Questions