Seek out the value and retrieve the corresponding row index

Question

Seek out the value and retrieve the corresponding row index

I'm facing an issue with this script. My goal is to search for today's date, match it, and return the column number (which is already working!). Now, I need a script that can do the same but with row numbers. The current script works fine if there is only one table on the sheet, however, since I have multiple tables, using values.length -1 will return the last row from the entire sheet. Additionally, each table may not have fixed row numbers, so the solution needs to be dynamic.

Here is the script I have developed so far:

function getTodaysTotal() {
  function toDateFormat(date) {
try {return date.setHours(0,0,0,0);}
catch(e) {return;}
 }

var values = SpreadsheetApp
  .openById("id")
  .getSheetByName("Q3 - W27 - 39")
  .getDataRange()
  .getValues();


var today = toDateFormat(new Date());
var todaysColumn = values[5].map(toDateFormat).map(Number).indexOf(+today);
var output = values[values.length - 1][todaysColumn];
var emailDate = Utilities.formatDate(new Date(today),"GMT+1", "dd/MM/yyyy");

Attached below is a screenshot of my table:

table

I hope this clarifies the situation. I already have the column number, now I just need to find the row number that contains "Total".

Thank you!

Regards,

javascript arrays google-apps-script google-sheets google-apps

Answer 1

Answer №1

If you want to achieve optimal speed, the best approach is to iterate through an array of data.

Check out the script below and integrate it into your existing code.

Assuming you are focusing on a single column, such as column C, here's an example:

for (i in values){
if (values[i][2]=='Total'){
Logger.log(i);
var nr = i
   }
}

The variable "nr" now stores the row number where the word "Total" was found in column C (indicated by [2] after [i]).

You can then utilize this variable for any purpose following the initial closing bracket.

Answer 2

If you want to achieve optimal speed, the best approach is to iterate through an array of data.

Check out the script below and integrate it into your existing code.

Assuming you are focusing on a single column, such as column C, here's an example:

for (i in values){
if (values[i][2]=='Total'){
Logger.log(i);
var nr = i
   }
}

The variable "nr" now stores the row number where the word "Total" was found in column C (indicated by [2] after [i]).

You can then utilize this variable for any purpose following the initial closing bracket.

Answer 3

Answer №2

Upon incorporating this into my script, I encountered an issue where the output returned as undefined. Interestingly, when utilizing the logger log function, I was able to obtain the correct value, however, it also resulted in an empty value (undefined). Consequently, I only require the initial value.

function getTodaysTotal() {
 function toDateFormat(date) {
try {return date.setHours(0,0,0,0);}
catch(e) {return;}
   }

var values = SpreadsheetApp
  .openById("id")
  .getSheetByName("Q3 - W27 - 39")
  .getDataRange()
  .getValues();

for (i in values){
 if (values[i][0]=='Total'){
 var nr = i;
 var newNr = parseInt(nr);

 //       Logger.log(nr);


var today = toDateFormat(new Date());
var todaysColumn = 
 values[5].map(toDateFormat).map(Number).indexOf(+today);
var output = values[newNr][todaysColumn];
 Logger.log(output);
var emailDate = Utilities.formatDate(new Date(today),"GMT+1", "dd/MM/yyyy");
//   Logger.log(todaysColumn);
//    Logger.log(output);
// return values[values.length - 1][todaysColumn];
}
 }




 if (output == undefined) {
GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="12777f737b7e52717d7f62737c6b3c717d7f">[email protected]</a>", "test data", "Today, " +emailDate +" we had no calls made or no values are inputed.");   
 }
 else if (output == "") {
GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b7d2dad6dedbf7d4d8dac7d6d9ce99d4d8da">[email protected]</a>", "test data", "Today, " +emailDate +" we had no calls made or no values are inputed.");
}
 else {

GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="20454d41494c60434f4d50414e590e434f4d">[email protected]</a>", "test data", "Today, " +emailDate +" we had " +output + " calls made.");

}
 }

Answer 4

Upon incorporating this into my script, I encountered an issue where the output returned as undefined. Interestingly, when utilizing the logger log function, I was able to obtain the correct value, however, it also resulted in an empty value (undefined). Consequently, I only require the initial value.

function getTodaysTotal() {
 function toDateFormat(date) {
try {return date.setHours(0,0,0,0);}
catch(e) {return;}
   }

var values = SpreadsheetApp
  .openById("id")
  .getSheetByName("Q3 - W27 - 39")
  .getDataRange()
  .getValues();

for (i in values){
 if (values[i][0]=='Total'){
 var nr = i;
 var newNr = parseInt(nr);

 //       Logger.log(nr);


var today = toDateFormat(new Date());
var todaysColumn = 
 values[5].map(toDateFormat).map(Number).indexOf(+today);
var output = values[newNr][todaysColumn];
 Logger.log(output);
var emailDate = Utilities.formatDate(new Date(today),"GMT+1", "dd/MM/yyyy");
//   Logger.log(todaysColumn);
//    Logger.log(output);
// return values[values.length - 1][todaysColumn];
}
 }




 if (output == undefined) {
GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="12777f737b7e52717d7f62737c6b3c717d7f">[email protected]</a>", "test data", "Today, " +emailDate +" we had no calls made or no values are inputed.");   
 }
 else if (output == "") {
GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b7d2dad6dedbf7d4d8dac7d6d9ce99d4d8da">[email protected]</a>", "test data", "Today, " +emailDate +" we had no calls made or no values are inputed.");
}
 else {

GmailApp.sendEmail("<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="20454d41494c60434f4d50414e590e434f4d">[email protected]</a>", "test data", "Today, " +emailDate +" we had " +output + " calls made.");

}
 }

Seek out the value and retrieve the corresponding row index

Answer №1

Answer №2

Similar questions

angular model bind include move

Can I deactivate JavaScript on my website directly from my server settings?

I'm having trouble with the colors not displaying correctly on my NextJS Tailwind navbar

"Revolutionary AJAX-enabled PHP social commenting system with multi-form support

Ways to have a list component smoothly descend when a dropdown menu is activated

Retrieve the complete content of a web page, including all HTML and JavaScript elements

Load images in the background prior to displaying them

Transitioning From Browserify to Webpack with Vue.js

Is there a way to arrange the characters in a string using Pointer Arrays?

Is it possible to install the lib ldap-client module in node.js?

Transform mysql data in bulk to json format with php

What is the best way to conceal a bootstrap directive tooltip in Vue.js for mobile users?

Styling the scrollbar for the PDF element on an HTML page

Step-by-step guide for adding an icon to the corner of a Material UI button

Accessing the 'comment' property within the .then() function is not possible if it is undefined

Determine whether a subdomain is present within an ajax file in php

Issue: Bidirectional binding functionality is not functioning properly within the user interface modal

Is there a way to connect a Button to a different page in VUE.JS?

The camera application is functioning smoothly on Marshmallow API 23, however, it crashes when trying to use the camera on API versions higher than 23

Switching Between Background Images in Angular