Automatically change color and move row when certain value is added

[Charlie C]

Hello,

I'm working on a sheet where I'm logging proposals to my company. Each row is a different proposal and columns include the title, the name of who proposed it, their company, who received the proposal, etc. One column is titled "status" and has a dropdown list that has options like "rejected" "accepted" "holding" etc.

Is it possible to make a script for when I change the status of any row to "rejected" or "holding" that it will automatically switch the row's fill color to grey and move it to the bottom of the data? I've been doing this manually but it would save me a lot of time if a script were possible for this. I've tried using the sort function but it doesn't happen automatically, I have to manually change it and then sort by color. Also when I sort by color it sends it all the way to the bottom of the sheet, not to the bottom of the data. 

Any help would be appreciated!

[R Tichy]

Yes, you can do this by creating an "onEdit" trigger for your google app script.

The easiest way would be to highlight the row and then choose a custom menu function because you'd be handing the script the row to operate on.  Then you do need to make a key or keys on which a sort at the end causes the sorting you desire.  I'm not sure how you are currently sorting by color?

[R Tichy]

similar example:

function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var menuEntries = [ {name: "Add new stock", functionName: "addstock"},

{name: "Monthly", functionName: "monthly"},

{name: "Add new team", functionName: "addTeam"},

{name: "Stock split", functionName: "split"},

{name: "Reset sheet", functionName: "reset"},

{name: "Reset costs", functionName: "setCosts"} ];

ss.addMenu("Admin", menuEntries);

AND

function addTeam() {

var team = Browser.inputBox("Add team","Enter new team name", Browser.Buttons.OK_CANCEL);

if (team != "cancel") {

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Teams");

ss.insertRowAfter(2);

var src = ss.getRange(2, 1, 1, ss.getMaxColumns());

src.copyTo(ss.getRange(3, 1));

ss.getRange(3, 2).setValue(team);

ss.getRange(3, 9, 1, 5).clearContent();

ss.getRange(3, 9, 1, 5).clearComment();

ss.getRange(3, 9, 1, 5).setFontColor("black");

var x = ss.getRange("locks").getColumn();

ss.getRange(3, x, 1, 12).clearContent();

ss.sort(2);

var cnt = ss.getLastRow();

// add a row to Standings page if needed

var st = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Standings");

var n = st.getLastRow();

if (n < cnt) {

st.insertRowAfter(n);

src = st.getRange(n, 1, 1, 17);

src.copyTo(st.getRange(n+1, 1));

}

// add a row to Monthly page if needed

st = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly");

n = st.getLastRow();

if (n < cnt) {

st.insertRowAfter(n);

src = st.getRange(n, 1, 1, 1);

src.copyTo(st.getRange(n+1, 1));

}

// Display message to enter picks

SpreadsheetApp.getActiveSpreadsheet().toast("Enter picks for new team");

}

}

[R Tichy]

First function adds a custom menu to the menu bar for the worksheet.

Second function responds to choosing "Add Team" from the menu options under "Admin"

Now attaching another function (would have been a better example for you called split() which acts on the highlighted row:

function split() {

var sheet = SpreadsheetApp.getActiveSheet();

var ratio = Browser.inputBox("Make sure row is selected. Enter split ratio", Browser.Buttons.OK_CANCEL);

if (ratio == "cancel")

return;

var row = SpreadsheetApp.getActiveRange().getRowIndex();

if (ratio != "") {

var sym = sheet.getRange(row, 1).getValue();

// change start price

var col = sheet.getRange("startprice").getColumn();

var r = sheet.getRange(row, col)

var price = r.getValue();

r.setValue(price/ratio);

r.setComment("Split " + ratio + ":1. Original price " + price);

// change monthly price

var r = sheet.getRange(row, col+1)

var price = r.getValue();

r.setValue(price/ratio);

// r.setComment("Split " + ratio + ":1. Original price " + price);

// change prices on picks

var teams = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Teams");

var c = teams.getRange("costs").getColumn();

var pr = teams.getRange("Picks");

var top = pr.getRow();

var picks = pr.getValues();

for (i=0; i < pr.getNumRows(); i++) {

for (j=0; j < 5; j++) {

if (picks[i][j] == sym) {

var s = teams.getRange(top + i, c + j);

price = s.getValue();

s.setValue(price / ratio);

s.setComment("Split " + ratio + ":1. Original price " + price);

}

}

}

}

}