As well as the indexOf method, I learned how to convert a 2-dimensional array into a 1-dimensional one, which I had never done before. Congratulations, very useful stuff.
How would find the row(s) which mentions today's date in the sheet? Given that you have today's date as string stored in a variable. This is especially useful when you dates/events stored in a sheet and you like to display the row associated with today on a website (not using Google Calendar).
I would like to be able to make the reading of a table with row and columns that is traversed to the end of the last row. I have used a for loop but I know the row total, my idea is not to worry about the row total especially if there are a large number of records. I appreciate your help. The channel is great
I'm having trouble with IndexOff with dates. I want to find the index in a column of dates that match a given date. I know I have to convert the date to something, but don't know how. A little help would be appreciated.
hi there! your videos have been helping a lot! great job on the videos... I have one question though >> will the indexOf result be affected when the values you are searching for is from query function result??
The getvalues() function changed for V8. The results returned shown in this video for getvalues() are wrong - it now returns a 2D array where each cell is an array... so getvalues now returns [[id],[sales_rep],[sales],[location]], which is NOT what is shown here, and consequently, the indexof function breaks. Any suggestions? The only one I've found is to map a simple function to extract each cell into a 1D array, and then run the indexOf function on it. Any other ideas?
It works the same way. It's gonna vary depending if you get them from a column or a row. you'll just have to map the array to a new array. Please watch my array map function video.
this is really a good tutorial. Thanks, What about mapping the 1D Array back to 2D Array to append the column? what would be the map function look like?
Thanks for the vid, How would you reference the row(or column )number found by the function? For example (this is what I've been trying): var rownumber = newdata.indexOf("8864-175W")
Could I use this in an onEdit? I'm still learning but for some reason it's not working. I want it to find the cell then activate it. function onEdit(e) { var range = e.range; var spreadSheet = e.source; var sheetName = spreadSheet.getActiveSheet().getName(); var column = range.getColumn(); var row = range.getRow(); var lastRow = sheetName.getLastRow(); var data = activeSheet.getRange(3, 2, lastRow, 1).getValues(); var newData = data.map(function(r){ return r[0]; }); if (sheetName == 'Stocks' && column == 2 && row == 2) {
sheetName.getRange(newData.indexOf(searchFor)+3, 2).activate(); } } Looking for the change to happen in "B2" then find the value in "B3:B140" then activate the cell.
Hi - I am literally copy and pasting from the array to make sure what I am searching for is in there, and it still returns -1. Am I missing something? var int = dates[0].indexOf("Date:"); Logger.log(int);
I can't tell by looking at this. It's case sensitive. Make sure you have the same thing in your worksheet with no extra spaces before and after with uppercase D and the colon too.
Its's Great Tuto. and learning. I'm seraching a solution to do a sort of Vlookuup function in Javascript... I have an tab in a sheet with description, name, and item code I would like to add a new column to this tab with the sales values, but without a Formula, with a script to avoid any re-calculation or dynamic update. What I did is to take the 80 000 line in a array with an additional column for this data And for each item code, so each line of the origin array find the index of the same item code in sales array and put the data in the row of the array then setvalues back on the gsheet this is sometoimes very long and I would like to understand or see if there is no faster or more efficient method to build one array by a matching key of 2 different arrays
Exemple could be taken from this tutorial data. First Spreadsheet is id/sales_rep/sales/ location like in the tuto Second Spreadsheet with a tab containing id/ Date of transaction And so you'd like to gather all information on the same tab without a a vlookup or importrange formula but with Js without touching to the the original tab, just adding a column on the right with matching data when existing. It's a case where you have 2 files coming from 2 differents datasource. Could be also First file - your order file -: order Number / Item code / quantity Second File -your referencing file- : Item code / description/price And you want to add in first file , the description and the price of the item code to make it more comprehensible and calculate the final price. My proposal is to make an 2D Array from original tab (with id and empty column) make an 2D array of the data (Id and Date) Search for each row of the original array the match in 2nd Array , with the indexof the second array, I able to have the Date of transaction for the ID, So i'm giving this value in the First array second column. when all row of the first array have been done, I'm writing on the spreadsheet the column with found data But this is somtimes long script and I'm wondering if there is no tips to go faster, easier, with map or other very good tips you're giving I saw some sites talking about hash of the table, using reduce method, but this is a little obscure for me...
@@arthurlegrandG reduce method is not good for this. I'm not why you need the 2nd empty column to do this, nevertheless, so long as you're doing the whole process within arrays and then write the whole array back to the sheet, then you should be fine. If you want to further optimize that, then you'll need to sort IDs in your second lookup table and search within sorted column using binary search. That should speed things up assuming you have a very large lookup table. Other optimizations can be done, but, frankly, I wouldn't worry about any of this unless you are a programmer with a few years of experience.
also, when they say hash table, they mean store it in object like structure like { 436536: 56.45, 565657: 34.99 } instead of array [[436536,56.45],[565657,34.99]]
As well as the indexOf method, I learned how to convert a 2-dimensional array into a 1-dimensional one, which I had never done before. Congratulations, very useful stuff.
You are the guy who makes me understand AppScript easily.
Requesting more videos on appScript
👍
Great Job!!
How would find the row(s) which mentions today's date in the sheet? Given that you have today's date as string stored in a variable. This is especially useful when you dates/events stored in a sheet and you like to display the row associated with today on a website (not using Google Calendar).
Looking forward to seeing how you would capture all matches, not just the first or last.
See array method filter video for that.
I would like to be able to make the reading of a table with row and columns that is traversed to the end of the last row. I have used a for loop but I know the row total, my idea is not to worry about the row total especially if there are a large number of records. I appreciate your help. The channel is great
I'm having trouble with IndexOff with dates. I want to find the index in a column of dates that match a given date. I know I have to convert the date to something, but don't know how. A little help would be appreciated.
🙏🏻
you best coach!
Thank you very much !!!
hi there! your videos have been helping a lot! great job on the videos...
I have one question though >> will the indexOf result be affected when the values you are searching for is from query function result??
Thank for the video. Would appreciate if you could share how to indexOf dates?
Thx, U SAVE MY LIFE:)
Thanks
Thank you very much for these brilliant series.
How to set format Date, sometime I get dd/mm/yy, sometime mm/dd/yyyy tks
The getvalues() function changed for V8. The results returned shown in this video for getvalues() are wrong - it now returns a 2D array where each cell is an array... so getvalues now returns [[id],[sales_rep],[sales],[location]], which is NOT what is shown here, and consequently, the indexof function breaks. Any suggestions? The only one I've found is to map a simple function to extract each cell into a 1D array, and then run the indexOf function on it. Any other ideas?
It works the same way. It's gonna vary depending if you get them from a column or a row. you'll just have to map the array to a new array. Please watch my array map function video.
you sholg probably be able to use findIndex with v8 as well.
myArray.findIndex(r => r[0] === "sales")
@@ExcelGoogleSheets findIndex function not found
Then you'll have to use map to restructure the array and then use IndexOf as usual. myArray.map(function(r) { return r[0] }).indexOf("sales")
ruclips.net/video/WA8QotNEVc4/видео.html
this is really a good tutorial. Thanks, What about mapping the 1D Array back to 2D Array to append the column? what would be the map function look like?
return [val]
@@ExcelGoogleSheets var Array2 = Array1.map(function(val){return [val];}); ?
@@printcenter5008 looks right.
Thanks for the vid,
How would you reference the row(or column )number found by the function?
For example (this is what I've been trying):
var rownumber = newdata.indexOf("8864-175W")
thank you very much
Could I use this in an onEdit? I'm still learning but for some reason it's not working. I want it to find the cell then activate it.
function onEdit(e) {
var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var column = range.getColumn();
var row = range.getRow();
var lastRow = sheetName.getLastRow();
var data = activeSheet.getRange(3, 2, lastRow, 1).getValues();
var newData = data.map(function(r){ return r[0]; });
if (sheetName == 'Stocks' && column == 2 && row == 2)
{
sheetName.getRange(newData.indexOf(searchFor)+3, 2).activate();
}
}
Looking for the change to happen in "B2" then find the value in "B3:B140" then activate the cell.
Which part of this tutorial talk about switch?
Hi - I am literally copy and pasting from the array to make sure what I am searching for is in there, and it still returns -1. Am I missing something?
var int = dates[0].indexOf("Date:");
Logger.log(int);
I can't tell by looking at this. It's case sensitive. Make sure you have the same thing in your worksheet with no extra spaces before and after with uppercase D and the colon too.
Date: you had a : in your text
Its's Great Tuto. and learning.
I'm seraching a solution to do a sort of Vlookuup function in Javascript...
I have an tab in a sheet with description, name, and item code
I would like to add a new column to this tab with the sales values, but without a Formula, with a script to avoid any re-calculation or dynamic update.
What I did is to take the 80 000 line in a array with an additional column for this data
And for each item code, so each line of the origin array find the index of the same item code in sales array
and put the data in the row of the array
then setvalues back on the gsheet
this is sometoimes very long and I would like to understand or see if there is no faster or more efficient method to build one array by a matching key of 2 different arrays
Hard to say without looking at the particular example.
Exemple could be taken from this tutorial data.
First Spreadsheet is id/sales_rep/sales/ location like in the tuto
Second Spreadsheet with a tab containing id/ Date of transaction
And so you'd like to gather all information on the same tab without a a vlookup or importrange formula but with Js
without touching to the the original tab, just adding a column on the right with matching data when existing.
It's a case where you have 2 files coming from 2 differents datasource.
Could be also
First file - your order file -: order Number / Item code / quantity
Second File -your referencing file- : Item code / description/price
And you want to add in first file , the description and the price of the item code to make it more comprehensible and calculate the final price.
My proposal is to
make an 2D Array from original tab (with id and empty column)
make an 2D array of the data (Id and Date)
Search for each row of the original array the match in 2nd Array , with the indexof the second array, I able to have the Date of transaction for the ID,
So i'm giving this value in the First array second column.
when all row of the first array have been done,
I'm writing on the spreadsheet the column with found data
But this is somtimes long script and I'm wondering if there is no tips to go faster, easier, with map or other very good tips you're giving
I saw some sites talking about hash of the table, using reduce method, but this is a little obscure for me...
@@arthurlegrandG reduce method is not good for this. I'm not why you need the 2nd empty column to do this, nevertheless, so long as you're doing the whole process within arrays and then write the whole array back to the sheet, then you should be fine.
If you want to further optimize that, then you'll need to sort IDs in your second lookup table and search within sorted column using binary search. That should speed things up assuming you have a very large lookup table.
Other optimizations can be done, but, frankly, I wouldn't worry about any of this unless you are a programmer with a few years of experience.
also, when they say hash table, they mean store it in object like structure like { 436536: 56.45, 565657: 34.99 } instead of array [[436536,56.45],[565657,34.99]]
That could be a good method, but needs to be tested.
Not useful...