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

admin管理员组

文章数量:1335386

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..

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..

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jan 7, 2022 at 18:40 Wicket 38.5k99 gold badges7878 silver badges193193 bronze badges asked Mar 19, 2014 at 4:59 jjones312jjones312 69566 gold badges1717 silver badges3030 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

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");
    }

  }
}

本文标签： javascriptGoogle Apps ScriptsetValue in cell based on for loop matchingStack Overflow