Locate elements in a 2D Array with Google Apps Script

Question

Locate elements in a 2D Array with Google Apps Script

I am working with an array containing the values:

[8:00 AM, 9:00 AM] //result of another function

My goal is to locate these values within a 2-Dimensional Array:

[
 [8:00 AM], 
 [9:00 AM], 
 [10:00 AM], 
 [11:00 AM], 
 [12:00 NN], 
 [1:00 PM], 
 [2:00 PM], 
 [3:00 PM]
]

and then update them to "Not Available"

The values in the array are retrieved from a Google Sheet using this script:

function testRow(){

  var lookDate = "Aug 28, 2019";
  var ss = SpreadsheetApp.openByUrl(url); 
  var ts = ss.getSheetByName("Time_Select");
  var checkData = ts.getRange(1, 1, 1, ts.getLastColumn()).getDisplayValues()[0];

  var index = checkData.indexOf(lookDate)+1;

  var timeValues = ts.getRange(2, index, ts.getLastRow()-1, 1).getValues();
  Logger.log(timeValues)

  /*var checkSplit = dateValues.join().split(",");
  var checkMe = checkSplit.indexOf(dataDisable[1]);
  var timeValues = ts.getRange(checkMe, index).getValue();*/
}

I attempted using this code (as shown above in the Google Script):

var checkSplit = dateValues.join().split(",");
var checkMe = checkSplit.indexOf(dataDisable[1]);
var timeValues = ts.getRange(checkMe, index).getValue();

but it did not give me the correct result. Do you have any suggestions or solutions on how I can search for values within the 2-dimensional array, find their location in the Google Sheet, and change the value to "Not Available"? Thank you in advance for your assistance.

here is the link for my google sheet: https://docs.google.com/spreadsheets/d/1lEfzjG1zzJVPMN8r-OpeZm6q9_IqSwk9DNCEY-q7ozY/edit?usp=sharing

javascript arrays google-apps-script google-sheets

Answer 1

Answer №1

Your timeValues output is in string format. Assuming that the elements within [8:00 AM, 9:00 AM] are also strings, here is a method to compare them and replace cell values with "Not Available" if they match:

function verifyRow(){

  var selectedDate = "Aug 28, 2019";
  var spreadsheet = SpreadsheetApp.openByUrl(url); 
  var timeSheet = spreadsheet.getSheetByName("Time_Select");
  var dataToCheck = timeSheet.getRange(1, 1, 1, timeSheet.getLastColumn()).getDisplayValues()[0];

  var position = dataToCheck.indexOf(selectedDate) + 1;
  
  var times = ['8:00 AM', '9:00 AM'];
  var timeValues = timeSheet.getRange(2, position, timeSheet.getLastRow() - 1, 1).getValues();
  
  for(var i = 0; i < times.length; i++){
    for(var j = 0; j < timeValues.length; j++){
      if(times[i] == timeValues[j][0]){
        timeValues[j][0] = "Not Available"
      }
    }
  }
  timeSheet.getRange(2, position, timeSheet.getLastRow() - 1, 1).setValues(timeValues);
}

In essence, you iterate through both arrays and replace matching elements in timeValues with "Not Available". Upon completing the loops, update your range with the modified values from the timeValues array.

Answer 2

Your timeValues output is in string format. Assuming that the elements within [8:00 AM, 9:00 AM] are also strings, here is a method to compare them and replace cell values with "Not Available" if they match:

function verifyRow(){

  var selectedDate = "Aug 28, 2019";
  var spreadsheet = SpreadsheetApp.openByUrl(url); 
  var timeSheet = spreadsheet.getSheetByName("Time_Select");
  var dataToCheck = timeSheet.getRange(1, 1, 1, timeSheet.getLastColumn()).getDisplayValues()[0];

  var position = dataToCheck.indexOf(selectedDate) + 1;
  
  var times = ['8:00 AM', '9:00 AM'];
  var timeValues = timeSheet.getRange(2, position, timeSheet.getLastRow() - 1, 1).getValues();
  
  for(var i = 0; i < times.length; i++){
    for(var j = 0; j < timeValues.length; j++){
      if(times[i] == timeValues[j][0]){
        timeValues[j][0] = "Not Available"
      }
    }
  }
  timeSheet.getRange(2, position, timeSheet.getLastRow() - 1, 1).setValues(timeValues);
}

In essence, you iterate through both arrays and replace matching elements in timeValues with "Not Available". Upon completing the loops, update your range with the modified values from the timeValues array.

Answer 3

Answer №2

What if we try this instead:

let updatedValues = timeValues.map((_value) => { return [_value]; });

Answer 4

What if we try this instead:

let updatedValues = timeValues.map((_value) => { return [_value]; });

Locate elements in a 2D Array with Google Apps Script

Answer №1

Answer №2

Similar questions

What yields greater performance in MongoDB: employing multiple indexes or creating multiple collections?

"How to ensure consistent styling for all buttons, including dynamically created ones, in an application by utilizing the jQuery button widget without the need for repetitive calls to

Troubleshooting jQuery's issue with dynamically adding input fields

Struggling to display a navigation menu on angular 1 with complex nested json structure

How can Reactjs access a configuration file?

Struggling with a findIndex problem in React and JavaScript

Perform an Ajax request with JQuery in an HTML document and transfer the response to a different HTML page

What is the way to utilize JavaScript for parsing Ruby JSON strings?

Array of Typed Objects in TypeScript

Tips for synchronizing arrays using the Angular Drag-Drop Component

Vue 3 - gaining access to the parent component

What could be the reason for the issue with Backbone.js and modal window breaking on IE9?

What is the reason for the malfunction of the top input function?

Break up each object in a Java ArrayList into individual elements

React-Redux showing no errors despite non-functional Redux store

Client connected via Socket.io is not receiving the message event

Strategies for managing large numbers within WebGL GLSL shader programs

Activate Pop-up for a single instance on BigCommerce

What makes React stand out as a server side rendering technology when compared to Angular?

A guide on inserting text annotations into text notes within a React Next.js 14 (with App Router) application