Convert the index match formula into a Google Script

Question

Convert the index match formula into a Google Script

I am currently consolidating tables from various sheets into one on Google Sheets. My goal is to create a function onedit(e) that can be paired with a loop function to execute this task.

=index($G$2:$G$8,match($A10&$I$1,arrayformula($B$2:$B$8&$E2:$E8),0),"").

As I am new to Google Script, I have been experimenting with formulas. However, the formula updates all adjacent cells, whereas I want it to match and remain static so that when column G is updated, the result is added to B10 without overwriting A10. (The cell I1 has the formula =today() for reference, but if the code can simply match with the date, that would be ideal!)

If you would like to see firsthand what I am referring to, here is the link to the sheet: https://docs.google.com/spreadsheets/d/1jviYYhI1V_bL8TMVvijR9vo_7sBFfktbhgblGNcYfrQ/edit?usp=sharing

The drop-down options in Column B2:B8 are linked to the list in A10:A18, just in case that makes a difference. Any assistance would be highly appreciated as I've been searching for a solution for three days now.

javascript if-statement google-apps-script match

Answer 1

Answer №1

Check out this solution:

function autoload() {
  var ss = SpreadsheetApp.getActiveSheet();
  var meeting = ss.getRange("B2:F8").getValues();
  var targetNames = ss.getRange("A10:A18").getValues();
  var writtingRange = ss.getRange("B10:F18").getValues();
  var updatedUser = targetNames;
  var writtingColumn = -1;
  var coolMatrix;

  for (var i = 0; i < meeting.length; i++) {
    var name = meeting[i][0]
    for (var j = 0; j < targetNames.length; j++) {
      if (targetNames[j][0] == name) {
        for (var z = 0; z < writtingRange[0].length; z++) {
          if (writtingRange[j][z] == '' && writtingColumn < 0) {
            writtingColumn = z;
            break;
          }
        }
        writtingRange[j][writtingColumn] = meeting[i][4];
        updatedUser[j] = true;
      }
    }
  }

  whiteSpaceRemover(writtingRange);
  ss.getRange("B10:F18").setValues(writtingRange);
}

function whiteSpaceRemover(multidimensionalArray) {
  for (var i = 0; i < multidimensionalArray.length; i++) {
    for (var j = 0; j < multidimensionalArray[0].length; j++) {
      if (multidimensionalArray[i][j] == '') {
        multidimensionalArray[i].splice(j, 1);
        multidimensionalArray[i].push('');

      }
    }
  }

  for (var i = 0; i < multidimensionalArray.length; i++) {
    if (multidimensionalArray[i][0] == '') {
      for (var j = 0; j < multidimensionalArray[i].length; j++) {
        if (multidimensionalArray[i][j] != '') {
          whiteSpaceRemover(multidimensionalArray);
        }
      }
    }
  }

  return multidimensionalArray;
}

I tested this on a duplicate of your sheet and it successfully assigns the correct note next to the name and a '-' if they were absent from the meeting. It currently requires manual execution, but can be set up with an onEdit() trigger if needed.

Answer 2

Check out this solution:

function autoload() {
  var ss = SpreadsheetApp.getActiveSheet();
  var meeting = ss.getRange("B2:F8").getValues();
  var targetNames = ss.getRange("A10:A18").getValues();
  var writtingRange = ss.getRange("B10:F18").getValues();
  var updatedUser = targetNames;
  var writtingColumn = -1;
  var coolMatrix;

  for (var i = 0; i < meeting.length; i++) {
    var name = meeting[i][0]
    for (var j = 0; j < targetNames.length; j++) {
      if (targetNames[j][0] == name) {
        for (var z = 0; z < writtingRange[0].length; z++) {
          if (writtingRange[j][z] == '' && writtingColumn < 0) {
            writtingColumn = z;
            break;
          }
        }
        writtingRange[j][writtingColumn] = meeting[i][4];
        updatedUser[j] = true;
      }
    }
  }

  whiteSpaceRemover(writtingRange);
  ss.getRange("B10:F18").setValues(writtingRange);
}

function whiteSpaceRemover(multidimensionalArray) {
  for (var i = 0; i < multidimensionalArray.length; i++) {
    for (var j = 0; j < multidimensionalArray[0].length; j++) {
      if (multidimensionalArray[i][j] == '') {
        multidimensionalArray[i].splice(j, 1);
        multidimensionalArray[i].push('');

      }
    }
  }

  for (var i = 0; i < multidimensionalArray.length; i++) {
    if (multidimensionalArray[i][0] == '') {
      for (var j = 0; j < multidimensionalArray[i].length; j++) {
        if (multidimensionalArray[i][j] != '') {
          whiteSpaceRemover(multidimensionalArray);
        }
      }
    }
  }

  return multidimensionalArray;
}

I tested this on a duplicate of your sheet and it successfully assigns the correct note next to the name and a '-' if they were absent from the meeting. It currently requires manual execution, but can be set up with an onEdit() trigger if needed.

Answer 3

Answer №2

function onEdit(event) 
    { var timezone = "GMT-5"; var timestamp_format = "MM/dd/yyyy"; // Timestamp Format. 
    var updateColName = "Student Name"; 
    var timeStampColName = "Date"; 
    var sheet, ss; 
    ss = SpreadsheetApp.getActiveSpreadsheet(); 
    sheet = ss.getActiveSheet();//Get the active sheet 
    var actRng = event.source.getActiveRange(); 
    var editColumn = actRng.getColumn(); 
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(4, 1, 1, sheet.getLastColumn()).getValues(); 
    var dateCol = headers[0].indexOf(timeStampColName); 
    var updateCol = headers[0].indexOf(updateColName); 
    updateCol = updateCol+1; 
    if (dateCol > -1 && index >=5  && index <= 36 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
    var cell = sheet.getRange(index, dateCol + 1); 
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format); cell.setValue(date); }

    function onEdit(){
        var ss = SpreadsheetApp.getActiveSheet();
        var meeting = ss.getRange("B2:F37").getValues(); 
        var targetNames = ss.getRange("A38:A90").getValues();
        var writtingRange = ss.getRange("B38:F90").getValues();
        var updatedUser = targetNames;
        var writtingColumn = -1;
        var coolMatrix;

        for(var i = 0; i < meeting.length; i++){ 
        var name = meeting[i][0] 
        for (var j = 0; j < targetNames.length; j++) { 
            if (targetNames[j][0] == name){
            for (var z = 0; z < writtingRange[0].length; z++) { 
                if (writtingRange[j][z] == '' && writtingColumn < 0) { 
                writtingColumn = z; 
                break; 
                }
            }
            writtingRange[j][writtingColumn] = meeting[i][4];
            updatedUser[j] = true; 
            }
        }
        }
        for (var w = 0; w < writtingRange.length; w++){
        if (writtingRange[w][writtingColumn] == ''){
            writtingRange[w][writtingColumn] = '-';
        }
        }
        ss.getRange("B38:F90").setValues(writtingRange);
    }}

Answer 4

function onEdit(event) 
    { var timezone = "GMT-5"; var timestamp_format = "MM/dd/yyyy"; // Timestamp Format. 
    var updateColName = "Student Name"; 
    var timeStampColName = "Date"; 
    var sheet, ss; 
    ss = SpreadsheetApp.getActiveSpreadsheet(); 
    sheet = ss.getActiveSheet();//Get the active sheet 
    var actRng = event.source.getActiveRange(); 
    var editColumn = actRng.getColumn(); 
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(4, 1, 1, sheet.getLastColumn()).getValues(); 
    var dateCol = headers[0].indexOf(timeStampColName); 
    var updateCol = headers[0].indexOf(updateColName); 
    updateCol = updateCol+1; 
    if (dateCol > -1 && index >=5  && index <= 36 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
    var cell = sheet.getRange(index, dateCol + 1); 
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format); cell.setValue(date); }

    function onEdit(){
        var ss = SpreadsheetApp.getActiveSheet();
        var meeting = ss.getRange("B2:F37").getValues(); 
        var targetNames = ss.getRange("A38:A90").getValues();
        var writtingRange = ss.getRange("B38:F90").getValues();
        var updatedUser = targetNames;
        var writtingColumn = -1;
        var coolMatrix;

        for(var i = 0; i < meeting.length; i++){ 
        var name = meeting[i][0] 
        for (var j = 0; j < targetNames.length; j++) { 
            if (targetNames[j][0] == name){
            for (var z = 0; z < writtingRange[0].length; z++) { 
                if (writtingRange[j][z] == '' && writtingColumn < 0) { 
                writtingColumn = z; 
                break; 
                }
            }
            writtingRange[j][writtingColumn] = meeting[i][4];
            updatedUser[j] = true; 
            }
        }
        }
        for (var w = 0; w < writtingRange.length; w++){
        if (writtingRange[w][writtingColumn] == ''){
            writtingRange[w][writtingColumn] = '-';
        }
        }
        ss.getRange("B38:F90").setValues(writtingRange);
    }}

Convert the index match formula into a Google Script

Answer №1

Answer №2

Similar questions

The behavior of the select menu is erratic when interacting with AJAX

Trigger an analytics event from a background chrome extension

What is the sequence in which Jest executes its tests?

The comparison between importing TypeScript and ES2015 modules

Transmit the identification to angularjs for the genuine content to be displayed

creating dynamic column headers with JavaScript

Can one use Javascript to access the Sharepoint REST API from an external source?

The problem with THREE JS OcclusionComposer: encountering "Cannot read properties of undefined (reading 'x')" error

Converting a string into a TypeScript class identifier

Ensuring the accuracy of nested objects through class validator in combination with nestjs

Transmit the document to Django using Ajax

How to maintain row highlight in jQuery datatable even after it is reloaded

The functionality of Jquery Ajax is functioning seamlessly on my local machine, but encountering issues

What could be causing the triggering of two AJAX requests in the given JavaScript code?

Guidelines on integrating Admob into Ionic framework

Is it feasible to maintain the color of an element after clicking a button with the use of arrays?

Add JSON elements to an array

Displaying HTML content using Typescript

Retrieve the value of a property in a JavaScript object by specifying a dynamic key for the property

``Error: GraphQL server unable to initiate due to a failure in the module.exports