Inventory Spreadsheet Script to Notify Below Minimum Qnty

Question:

( by RMS Solutions )

I am creating an inventory spreadsheet and am having trouble wrapping my head around this.

Here is the ultimate thing I would like to happen.  

If any cell (B2, C2, D2, etc) is less than it's corresponding column cell (B1, C1, D1 etc), then email 3 people 

So I would need all B cells to point to B1 for the answer.  

Overall: I have people using google forms to fill out inventory #'s each day.  Once that # hits a # lower than we'd like to see in inventory, I want 3 people to be notified so those items can be purchased.  There may be a better way to do it but I am having trouble figuring it out. 

Thanks

=========

Well, apparently I have no idea because I am completely confused!  This may be above my head.  

I have the form set up, it is pulling in data.  I currently have it set up so that if any cell is lower than the target # for that column, it highlights it in red, which is nice.  I just can't figure out how to write the script to pull the correct data each day for my particular situation.  

There is a very simple option to email any updates to a single person (via the tools menu....notification rules) and I love everything about it except for the fact that it doesn't show my highlighted cells.  If that email could send to multiple people AND show the cells highlighted in red, I think that would be just about perfect.

=========

Yes, they do show the rest of the spreadsheet, but it highlights the most recent changes.  It just doesn't show the highlighted cells in red, which is what the person receiving the email would need so they would know what to purchase.  

In looking at that initial link you sent, it looks like maybe just maybe a script could pull the highlighted cells into an email, only showing what would need to be purchased.  Now, that would be cool. I just am not understanding how I would set that up using the gradebook script.

I think I'm at a point that I just need to hire someone to write the script.  I don't have any knowledge of script writing and it looks like something I'd need several hours to understand which I don't have right now.

Solution:

Have a look at the following screenshot of sheet "Form Responses 1":

Have a look at the following screenshot of sheet "Report":

In the Sheet "Report" I have the following formula in Cell C3:

=transpose(query('Form Responses 1'!A2:Z;"select B,C,D where A is not null order by A desc limit 1"))

and following in Cell C1:

=query('Form Responses 1'!A2:Z;"select A where A is not null order by A desc limit 1")

Have a look at the following script code:

///////////////

function Check() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Report");  
  var values = sheet.getRange("A3:C5").getValues();
  var flag = false;
  var email = "kishan.pionero@gmail.com";
  var cc = "xyz2@example.com,xyz3@example.com";
  
  for(var i=0;i<values.length;i++)
  {
    if(values[i][2]<values[i][1]) { 
      sheet.getRange(i+3,3).setBackground("Red");
      flag=true;
    }
    else sheet.getRange(i+3,3).setBackground("White");
  }
  SpreadsheetApp.flush();
  if(flag==true) sendEmail(email,cc);
};

function sendEmail(email,cc) {

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); 
  var sheetName = "Report";
  var subject = "Current Qnty of some items have gone below Minimum Qnty Required";
  var body = "This is an automated email to notify you that 'Current Qnty' of some items have gone below 'Minimum Qnty Required'. Please, check the attachment.";
  
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  
  var requestData = {"method": "GET", "oAuthServiceName": "google", "oAuthUseToken": "always"};

  var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="
      + ssID + "&gid=0&portrait=true" +"&exportFormat=xls";
    
  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();
  MailApp.sendEmail(email, subject ,body, {cc:cc,attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"application//xls"}]}); 

};

///////////////

In the above code change my email address and CC to your's. And also change the sheet name and cell as per your requirement.

And then set the time drive trigger on function Check().

To set the trigger you have to go to "Script editor" and there you have to click on the trigger icon below the "Publish" tool bar command. Have a look at the screenshot below:

Then click on "Add a new trigger", then in Run select "readCell" and in Events click on "Time-driven" and then select daily or every 6 hours (or whatever you prefer) to execute this trigger.

I hope this script would have solved your problems of getting notification when a value of predefined cell gets over a predefined value.

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive