CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
swolfe2
swolfe2

Reputation: 451

Is there an Index(Match()) Equivalent for Apps Script?

First off: total JS/Apps Script novice here. : )

I'm trying to replace some Google Sheets formulas I have with Apps Script, and was wondering how I would code the following if statement:

var EMAIL_SENT = "EMAIL_SENT";

function onFormSubmitTest(e) {
    //Open spreadsheet based on URL
    var ss = SpreadsheetApp.openByUrl('sheetnamehere');
    //Set as Active
    SpreadsheetApp.setActiveSpreadsheet(ss);
    //Set Tabs as Variables
    var Rsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emailer");
    var Lsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Unique Databases");

    //----Set last Rows as Variables----
    // Gets Values from different sections, filters arrays to non-null values off of [0] index
    var formResponses = Rsheet.getRange("N3:N").getValues().filter(function(row) {return row[0]}).sort();
    var databaseNames = Lsheet.getRange("A2:B").getValues().filter(function(row) {return row[0]}).sort();
    var developerNames = Lsheet.getRange("F2:F").getValues().filter(function(row) {return row[0]}).sort();

    // Attempt finding current process owner's e-mail address

    // Emailer Rows to Process
    var startRow = 3; // First row of data to process.  This is required because of various formatting messed up the opportunity for Last Row being the only row in the dataRange.

    // Number of rows to process
    var numRows = formResponses.length;

    // Fetch the range of cells
    var dataRange = Rsheet.getRange(startRow, 1, numRows, 30);

    // Fetch values for each row in the Range.
    var data = dataRange.getValues();

The statement I'm trying to load into a variable is if the value of var data [16] = var databaseNames [0], return var databaseNames [1]. I'm pretty sure that all arrays are coded correctly to be sorted in ascending order for faster indexing.

Thanks!

Any help/advice you all could provide would be greatly appreciated.

Upvotes: 4

Views: 2839

Answers (2)

Nico
Nico

Reputation: 316

You are referring to data[16] and databaseNames[0], but note that range.getValues() returns a two-dimensional array. databaseNames[0] would be the first array element in dataBaseNames[], which is equivalent to the first row in the range.

It's not clear to me whether you are looking to return an array or a single value. If you are just trying to figure out how to return the value in databaseNames[i][1] when databaseNames[i][0] = x, something like this should do it:

findMatch = function(x) {
  var returnValue;
  // assuming databaseNames exists in parent or global scope...
  databaseNames.forEach(function(row){
    if (row[0] === x) returnValue = row[1];
  });
  return returnValue;
}

If you are looking to return an array of values that match data[i][16], you could do var newArray = data.map(findMatch), and modify the function to set the comparison value to x[16] instead of x.

Upvotes: 1

Jason Allshorn
Jason Allshorn

Reputation: 1625

I'm still not totally clear, but I think this might be what you are looking for. In your question perhaps edit it give a concrete example of the structure and nature of the data. 

var result = "";

var data = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32];
var databaseNames  = [16,19];

var a = data[16]; // 16
var b = databaseNames[0]; //16

if(a === b){
   result = a //16
}

Upvotes: 2

Related Questions