CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Ken_LYK
Ken_LYK

Reputation: 85

Set specific cells with values to trigger with google script one by one

Before VS After looks after running my current codeI'm trying to trigger an action that will allow me to send my current google script into an app. I tried to look around google and came out with what I have now.

I have a drop-down list at column "K3:K100". There are two options (Slack & Not Yet).

I want my script to be able to run when I select "Slack" and will change to "slacked" once the script is done running while if any of the columns are selected as "Not Yet" it will not run or do anything. Currently, this code works but it turns the whole column of K3:K100 to "Slacked" including the column that are written "Not yet" selected. Please help! I'm new to this.

TLDR: I want K3 column when "Slack" selected turns to slacked with google script while K4 column will not turn to "Slacked" instead just be "not yet" when the drop list is selected as I have programmed the script to run based on "Slack".As of now, it overwrites everything on column K3:K100 to "Slacked" once the script is run. I want those that are currently selected as "Not Yet" to be as it is not overwritten. 

function SendSlack(){

  // get information from the range of 
  var depositid = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = depositid.getSheetByName("MYSHEETNAME");
  var lastrow = sheet.getLastRow();

  var range = sheet.getRange(2,1,lastrow-3,12).getValues();

  // create timestamp to mark when communication was sent
  var timestamp = new Date();

  // loop over range and send communication if "SLACK" option chosen
  for (var i = 0; i < range.length; i++)
  {SpreadsheetApp.getActiveSheet().getRange('K3:K100').setValue('Slacked'); // this makes it to "slacked when you press slacked and run script"
    if (range[i][10] == "Slack")
      sendToSlack(range[i]);

    };
  }
}

Upvotes: 0

Views: 71

Answers (2)

Jimmy Affatigato
Jimmy Affatigato

Reputation: 116

If I understand correctly, you probably want this. 

const range = SpreadsheetApp.getActiveSheet().getRange("K3:K100").getValues()
for (var i = 0; i < range.length; i++) {
    if (range[i][10] == "Slack") {
        sendToSlack(range[i]);
        range.setValue("Slacked");
    }
}

The setValue("Slacked") should be inside the if check. You don't need a case for "Not Yet" because it wouldn't do anything anyway. Notice I also put the range in a const outside the loop. Those get functions are costly.

Upvotes: 1

Cooper
Cooper

Reputation: 64072

Try this:

function SendSlack(){
  var depositid = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = depositid.getSheetByName("MYSHEETNAME");
  var rg=sheet.getRange(2,1,sheet.getLastRow()-1,12);
  var vs=rg.getValues();
  //var timestamp = new Date();
  for (var i=0;i< vs.length;i++) {
    if (vs[i][10]=="Slack"){
      sendToSlack(vs[i]);
      sheet.getRange(i+2,11).setValue("Not Yet");//assume column 11 is validation dropdown
    }
  }
}

Upvotes: 2

Related Questions