CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
M. Niel
M. Niel

Reputation: 11

Fill column based on row data

I'm looking to use a button to run a script within a Google Sheet. When the button is pressed, I'd like a column to populate with "Approved" for a given row, based on the user that's logged in and the email that's listed in the row.

For example, Column A has a list of different email addresses. Press a button, and column B is filled with "Approved" if your login email address is the same as what's in column A. Ideally, it could search and make sure the entry in column B isn't already populated with "Rejected". Think about a supervisor going through a list, and instead of using a pull-down for "Approved" on each line, they can hit a button and it would approve all of the entries their email is attached to.

I have it working, but it's slow and inefficient as it goes through each row, evaluates, then populates the appropriate column. It also doesn't set the approval for the last row of data. It runs through each row, populates, but doesn't fill the Approved on the last row of data entered.

I'd like to figure out how to do this with an array to make it more efficient.

Here is what I have so far:

function supervisorApproval(){
//This function will approve all of the peole in the respected supervisors section

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AS");

var approverLogin = Session.getActiveUser().getEmail();
var ui = SpreadsheetApp.getUi();

//Calculate which row is the last row (last entry)
var column = sheet.getRange('W:W');
var values = column.getValues();
var lastrow = 1;

//this is inefficient code, doesn't write "approved" for last row of data
while (values[lastrow][0] != ""){
var supEmail = sheet.getRange(lastrow, 23).getValue();
var approvalCell = sheet.getRange(lastrow, 27);

if (approverLogin == supEmail){
  approvalCell.setValue("Approved");
}

lastrow++;
}

}

Upvotes: 1

Views: 1998

Answers (1)

Tesseract
Tesseract

Reputation: 8139

It should be a lot faster if you read and write values to the spreadsheet only once. Try this.

function supervisorApproval() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("AS");

  var approverLogin = Session.getActiveUser().getEmail();
  var ui = SpreadsheetApp.getUi();

  var range = sheet.getRange(1, 23, sheet.getLastRow(), 5);
  var values = range.getValues();
  for(var i = 0; i < values.length; i++) {
    var row = values[i];
    var supEmail = row[0];
    if (approverLogin === supEmail){
      row[4] = "Approved";
    }
  }
  range.setValues(values);
}

Upvotes: 1

Related Questions