Search code examples
javascriptgoogle-sheetsgoogle-apps-scriptcolorstriggers

Cancel Script is a cell is RED

I have a script that works perfectly but I have some staff that will run the script even if a cell is red which was put in place to prevent missing data being sent out.

Is there a way I can add a line in my current script so it wont run if there is a red cell present?

I have looked at various suggestions but not sure on how to add the line into my current script that works perfect for what we need.

My situation:

I have a script that works perfectly but I have some staff that will run the script even if a cell is red which was put in place to prevent missing data being sent out.

Is there a way I can add a line in my current script so it wont run if there is a red cell present?

Current Script is:

function Email8() {
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

//var email = Session.getUser().getEmail();

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("G9:H10");

var emailAddress = emailRange.getValue();

var subject = "S/A - Darrin";

var body = ("Thank you for your business.");


var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
var shID = getSheetID("S/A - Darrin") //Get Sheet ID of sheet name "Master"
var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID+"&gid="+shID;

var result = UrlFetchApp.fetch(url , requestData);  
var contents = result.getContent();

var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("F5:G8").getDisplayValues().flat().join(",");var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A21").getDisplayValue();
MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)], bcc });

var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();

  var email_ID1 = "[email protected]";
 
  var subject = "CTC Sales Agreement.";

  var body = ("Attached is your document. \n \Thank you for your business");


  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  var shID = getSheetID("Xero Invoice - Darrin") //Get Sheet ID of sheet name "Xero Invoice - Darrin"
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=csv&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  MailApp.sendEmail (email_ID1, subject ,body, {attachments:[{fileName:sheetName+".csv", content:contents, mimeType:"application//csv"}]});

};
function getSheetID(name){
var ss = SpreadsheetApp.getActive().getSheetByName(name)
var sheetID = ss.getSheetId().toString() 
return sheetID
}

Speadsheet Example below. H1 is red so this needs to stop the script running. Various other cells could also be red so was hoping to use a range the "A1:K90" on the original one. https://docs.google.com/spreadsheets/d/1JDHDWntR6YVQqgb1lki90dvhRJgFagVAtrrS86H_GBg/edit?gid=0#gid=0

Solution

  • Use getBackgrounds()

    Based from your post, if there is a single cell in red (#ff0000 in hex), your script should not run. You may add an if-else statement before your script so that your script will first check for any cells with red background before running your script. You may use the following sample as basis for your script:

    function checkingForRed() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //change to openByID() and/or openSheetByName() if the sheet to be checked is external.
  var data = ss.getRange(1,1,10,5).getBackgrounds(); //Using 10 and 5 because my sample sheet has no data except for background colors green and red
  var checkForRed = data.filter(x=>x.includes('#ff0000')).length; //will return 0 if there is no red background color
  if (checkForRed) {
    console.log("Script will not run due to cells in red.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Script will not run due to cells in red."); //toast will be visible in sheets ui
  }
  else {
    console.log("Running the script. Place your script inside the else.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Running script."); //toast will be visible in sheets ui
  }
}

    You can either call your script within the else statement or add your entire script within the else statement. You may modify the range in line 3 and change 10 and 5 to ss.getLastRow() and ss.getLastColumn() since my test sheet has no data and only looks like this (because you have not provided any sample spreadsheet):

    sample sheet

    If I run my sample script, the Google Apps Script execution log should look like this:

    execution log

    with a toast notification on sheets like this:

    toast notification

    Reference:

    UPDATE

    You can try modifying the sample script provided like this:

    function checkingForRed() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //change to openByID() and/or openSheetByName() if the sheet to be checked is external.
  var data = ss.getRange(1,1,10,5).getBackgrounds(); //Using 10 and 5 because my sample sheet has no data except for background colors green and red
  var checkForRed = data.filter(x=>x.includes('#ff0000')).length; //will return 0 if there is no red background color
  if (checkForRed) {
    console.log("Script will not run due to cells in red.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Script will not run due to cells in red."); //toast will be visible in sheets ui
  }
  else {
    console.log("Running the script.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Running script."); //toast will be visible in sheets ui
    Email8(); //<<<<<<<<<<<<<<<<<<<<<<<<This will call your main script `Email8()`
  }
}

    or just place everything under else statement:

    function Email8() {
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("G9:H10");

  var emailAddress = emailRange.getValue();

  var subject = "S/A - Darrin";

  var body = ("Thank you for your business.");

  ///////////////////////////////////////////////////////////////////////////////////
  //added script
  var backgroundArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A1:K").getBackgrounds();
  var checkForRed = backgroundArray.filter(x => x.includes('#ff0000')).length;
  if (checkForRed) {
    console.log("Script will not run due to cells in red.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Script will not run due to cells in red."); //toast will be visible in sheets ui
  }
  else {
    console.log("Running the script.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Running script."); //toast will be visible in sheets ui

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

    var requestData = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    var shID = getSheetID("S/A - Darrin") //Get Sheet ID of sheet name "Master"
    var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=pdf&id=" + ssID + "&gid=" + shID;

    var result = UrlFetchApp.fetch(url, requestData);
    var contents = result.getContent();

    var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("F5:G8").getDisplayValues().flat().join(","); var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A21").getDisplayValue();
    MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)], bcc });

    ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

    sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

    var email_ID1 = "[email protected]";
    subject = "CTC Sales Agreement.";
    body = ("Attached is your document. \n \Thank you for your business");
    requestData = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    shID = getSheetID("Xero Invoice - Darrin") //Get Sheet ID of sheet name "Xero Invoice - Darrin"
    url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=csv&id=" + ssID + "&gid=" + shID;

    var result = UrlFetchApp.fetch(url, requestData);
    var contents = result.getContent();

    MailApp.sendEmail(email_ID1, subject, body, { attachments: [{ fileName: sheetName + ".csv", content: contents, mimeType: "application//csv" }] });
  } //<<<<<<<<<<<<<dont forget this bracket
};
function getSheetID(name) {
  var ss = SpreadsheetApp.getActive().getSheetByName(name)
  var sheetID = ss.getSheetId().toString()
  return sheetID
}