formulasgoogle sheets
Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other cell of choice). I've confirmed I can do this with a series of nested IF statements, but am looking for something more efficient. I'm not too terribly familiar with writing my own scripts, but can if someone can give general direction.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Instead of NOW() you could try TODAY(). It's still a volatile function but its result will change only once per day.
If you anyway go for a script, use a time-driver installable trigger.
NOTES:
Example:
The following is an function to be called by a time-driven trigger that will add the current month number (1-12) to Sheet1!A1.
function respondeToDailyTrigger(){
var now = new Date(); // Similar to Google Sheets NOW();
var jsMonth = now.getMonth(); // 0 for January, 1 for February, and so on
var spreadsheet = SpreadsheetApp.getActive(); // destination spreadsheet
var sheet = spreadsheet.getSheetByName('Sheet1'); // destination sheet
var range = sheet.getRange('A1'); // destination cell
range.setValue(jsMonth + 1); // add the month number (1-12) to the destination cell
}
Best Answer
From rev 1:
Please note that the month is above the value.
Formula
Explanation
$C1:$1refers to the headers rowC2:2refers to the values row.IFreturns an array where values equal and greater than 0 are included and blank is included for values that are less than 0.JOINcreates a string using a comma to separate array values.SPLITsplits the string omitting the blank members.INDEXreturns the first member.