Google Apps Script - .setValue in cell based on for loop matching

Question

I'm attempting to grab the values from a data range, loop over the data, match a value in that data, then based on a matching value, update cell located a few columns over.

I'm able to locate value to match, but I'm having a hard time understanding how to update the cell a few columns over. Below is the code I've gotten so far minus the .setValue piece.

var trackingSS = 'Spreadsheet 1';
var decisionSS = 'Spreadsheet 2';

function grabRequestID() {
  var ss = SpreadsheetApp.openById(decissionSS);
  var range = ss.getActiveSheet().getRange(ss.getLastRow(), 2, 1, 1)
  var requestID = range.getValue(); 

  return requestID;
}

function managersDecision() {
  var ss = SpreadsheetApp.openById(trackingSS)
  var sheet = ss.getSheetByName('Requests');
  var data = sheet.getDataRange().getValues();
  var requestID = grabRequestID();

  for (var i=0; i < data.length; i++) {
    for (var j=0; j < data[i].length; j++) {
      if (data[i][j] == requestID) {
        Logger.log('found it');
      }    
     }  
    }
   }

As you can see there are two functions. managersDecision() reads in all the data from spreadsheet 1. It then calls grabRequestID() and uses this value (from spreadsheet 2) as the matching criteria as it loops over the data from spreadsheet 1. Currently it will locate and find the match.

What I want to have happen now, is based on the match, go over two columns in the same row and update the cell value to "approved" or "denied" based on successfully finding a match.

I'm a bit lost on how to get it to write to the cell. Should I try and identify the row its in and then work to set the value? Maybe grab the entire row the match is in (into an array) and then rewrite the row?

Any assistance would be appreciated..

Harold Harold · Accepted Answer · 2014-03-19T07:25:17

To set a value you just need to take in count that you are working with an array that start at zero, but in the spreadsheet we start counting at one. You'll also need to be sure that you are trying to write in an existing cell. So prior writing, add the necessary column.
I didn't wrote the "denied" part as it was going through all the cell of the spreadsheet, but I wrote a second version of the managersDecision function where I only go through one column and here I took care of that denied part.

here the code:

var trackingSS = 'Spreadsheet1';
var decisionSS = 'Spreadsheet2';
function grabRequestID() {

  var ss = SpreadsheetApp.openById(decisionSS);
  var range = ss.getActiveSheet().getRange(ss.getLastRow(), 2, 1, 1)
  var requestID = range.getValue(); 
  Logger.log("requestID= "+requestID);
  return requestID;
}

function managersDecision() {
  var ss = SpreadsheetApp.openById(trackingSS)
  var sheet = ss.getSheetByName('Requests');
  var data = sheet.getDataRange().getValues();
  var requestID = grabRequestID();

  for (var i=0; i < data.length; i++) { // going through all the rows
    for (var j=0; j < data[i].length; j++) { // this is going through all the cell of a row
      if (data[i][j] == requestID) {
        Logger.log('found it');
        var row = Number(i)+1;
        var col = Number(j)+1+2;
        while(sheet.getMaxColumns()<col){
          sheet.insertColumnsAfter(sheet.getMaxColumns(),col-sheet.getMaxColumns());
        }
        sheet.getRange(row, col).setValue("approved");
      }    
    }  
  }
}


function managersDecision2() {
  var ss = SpreadsheetApp.openById(trackingSS)
  var sheet = ss.getSheetByName('Requests');
  var data = sheet.getRange("A:A").getValues()
  var requestID = grabRequestID();

  var col = 1+2;
  while(sheet.getMaxColumns()<col){
    sheet.insertColumnsAfter(sheet.getMaxColumns(),col-sheet.getMaxColumns());
  }

  for (var i=0; i < data.length; i++) { // going through all the rows
    var row = 1+i;
    if (data[i][0] == requestID) {
      Logger.log('found it');     
      sheet.getRange(row, col).setValue("approved");
    }
    else if(data[i][0] !=""){
      Logger.log(row)
      sheet.getRange(row, col).setValue("denied");
    }

  }
}

Google Apps Script - .setValue in cell based on for loop matching

1 Answers