CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsvlookup
Dylan
Dylan

Reputation: 1

How to create a note on a Spreadsheet cell based on the value of the selected cell

To get this specific function to work, I'm trying it out in a simple test sheet.

I've got two sheets(STATUS and FEBRUARI) in the FEBRUARI sheet I've selected a certain cell. This cell has a value. What I want the script to do is to look at that value, find that value in the STATUS sheet(say it finds it in A1) and return the value in B1 to a cell note in the selected cell in the FEBRUARI sheet. As example: in the cell it says "Project 6" and the cell-note gives info about this project.

This is what I got. This gives me a certain value(-1) but it doesn't seem to matter where i put the lookupvalue.. it always returns -1.

    // My Script

function noteSetter() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lookupvalue = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  var sheet = ss.getSheetByName("STATUS"); //source sheet
  var sheet2 = ss.getSheetByName("FEBRUARI"); //result sheet
  var cellnote = SpreadsheetApp.getActiveSheet().getActiveCell();
  var lc = sheet.getLastColumn()
  var lookup = sheet.getRange(1,1,1,lc).getValues() //
  var index = lookup.indexOf(lookupvalue)


  cellnote.setNote(index);


  // This part will actually run the script once it's up and running
  function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Set cell note",
    functionName : "noteSetter"
  }];
  sheet.addMenu("Scripts", entries);
};


}

Upvotes: 0

Views: 138

Answers (2)

Aerials
Aerials

Reputation: 4419

According to Google preferably use getCurrentCell() instead of getActiveCell() because it returns the current highlighted (or selected) cell.

Also your onOpen() function should be outside of your noteSetter() function otherwise it is not being called when the spreadsheet opens.

The following code will do what you want for a sheet like yours. If the data order is altered you have to alter the range formulas accordingly.

/* 
 * This function will run when the Spreadsheet is opened and,
 * will add a Menu item for the noteSetter function 
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Set cell note",
    functionName : "noteSetter"
  }];
  sheet.addMenu("My Menu", entries);
};

function noteSetter() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("STATUS"); //source sheet
  var sheet2 = ss.getSheetByName("FEBRUARI"); //result sheet
  var noteCell = sheet2.getCurrentCell();
  var lookUpValue = noteCell.getValue();

  // Search through Col C in "STATUS" sheet to get the matching row
  // You need to transpose the lookUpRange 
  var lookUpRange = sheet.getRange(2,3,sheet.getDataRange().getLastRow(),1).getValues();
  lookUpRange = transpose(lookUpRange);

  var index = lookUpRange[0].indexOf(lookUpValue);  // Starts at 0
  var row = index + 2;  // One for the omitted header and one because rows start at 1
  var note = sheet.getRange(row,7).getValue();

  noteCell.setNote(note);

}

// You need to transpose to avoid looping through the array
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Upvotes: 0

Cooper
Cooper

Reputation: 64062

  var lookup = sheet.getRange(1,1,1,lc).getValues();
  var index = lookup.indexOf(lookupvalue)

The first line returns a 2D array. indexOf() only works on flattened arrays. Try using:

var lookup = sheet.getRange(1,1,1,lc).getValues()[0];

Upvotes: 0

Related Questions