google sheets notes via script based on lookup

Question

This might be a complete failure for me, but I figured i'd ask anyway and see if it leads me to my answer.

What i want to do is have a script

examine the cell i'm in
essentially do a VLookup on a table in another worksheet in the same workbook,
put the values found from that lookup into a comment/note for that cell.

I'd need a macro to do this the first time, and i'd want the script to run on change any other time.

The google spreadsheet is being used as a schedule. Columns are grouped by day, and each day has several shifts. Rows indicate the hours that are being worked. Cell value tells the user the initials of the person working that shift on that day at that time.

I know how to set a comment. I know how to construct a loop to loop through each column and check to see if there is a value.

What i don't know is how to lookup the cell's value and return data from the other table.

Can anyone provide me with this small piece of knowledge?

Preactive Preactive · Accepted Answer · 2015-04-16T21:29:13

Here is a short version of a Lookup Function I have. I have a more complex one if you would like but just let me know and I can post it in another reply.

//------------------Single Cell VLookup return var
/*
 [129   Santa Clara BA  1234    abce    W   ]
 [130   Los Feliz   LA  5678    deff    I   ]
 [131   Sand City   BA  9112    ghig    C   ]
 finderLookUP('Data','A:G',130,0,2) would return 'LA'
*/    
function finderLookUP_(Sheet,Range,lookupItem,IndexOffset,IndexOffsetReturn)   // finderLookUP(Ref Sheet, Ref Sheet Range, Current lookup Item, Ref Sheet Column for Current lookup Item , Ref Sheet Column for qualified Current lookup Item RETURN)
    {                                                                             
     var data = SpreadsheetApp.getActive().getSheetByName(Sheet).getRange(Range).getValues();         //Syncs sheet by name and range into var
     for(nn=0;nn<data.length;nn++)
      {
        var Sending = "";
        if (data[nn][IndexOffset]==lookupItem)
        {
          var Sending = data[nn][IndexOffsetReturn];
          return Sending;
        } 
      }
      if(isEmpty_(Sending)==true)
      {
        var Sending = "#N/A";
        return Sending;
      }

    }

google sheets notes via script based on lookup

1 Answers