Javascript on Google Sheets - Get Column Number from Prompt Input

Question

I have two sheets on a Google Spreadsheet. One has a lot of information and references and the other has the same reference in the first cell of a column with link names and links below. I am trying to get around the "no multi-hyperlinking in one cell" limitation by having the user input the reference they want to search and then searching through the second sheet to find the reference and have a pop-up box with the links.

So far, I am able to get the links from the second spreadsheet column and display them in a pop-up box with this code:

    function main(){
      var column = SearchAndFind()
      showURL(getLinks(column))
    }

    function getLinks(col){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sh = ss.setActiveSheet(ss.getSheets()[1]);
      var cell = ss.getActiveCell();
      var values = sh.getDataRange().getValues();
      var myArray = []

      for(n=1;n<values.length;++n){
        var cell = values[n][col] ; 
        myArray.push(cell);
      }
      return myArray;
    }

    function showURL(data){ 
      var app = UiApp.createApplication().setHeight(40+8*data.length).setWidth(200);
      app.setTitle("Show URLs");
      var panel = app.createVerticalPanel();
      app.add(panel);
      for(var d=0 ; d<data.length;d=d+2){
      var link = app.createAnchor(data[d],data[(d+1)]);
      panel.add(link);
      }
      var doc = SpreadsheetApp.getActiveSpreadsheet();
      doc.show(app);
      return;
      }

When I hard-coded a random column number to the getLinks function and it worked fine but I need to be able to get the column number from a user search of the first cell in each column in the second sheet.

This is the code I have right now that doesn't work:

//I know that it will always be the second sheet on the spreadsheet
//Search the column headers on the second sheet
//When one matches, return the index
function SearchAndFind(){
      //Make the 2nd sheet active
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sh = ss.setActiveSheet(ss.getSheets()[1]);
      var range = sh.getRange(1, 1, sh.getMaxRows(), 1);
      var values = range.getValues();
      //Get the user input for the text they want to search
      var ui = SpreadsheetApp.getUi();
      var search = ui.prompt('Enter the ID: ');
      var searchString = search.getResponseText()

      //for loop to iterate through the first row and find the matching cell
      //return the index of that column
      for (n = 0; n < values[0].length; n++){
        var cell = values[0][n]
        if (cell === searchString){
          return n;
        }
      }
    }

When I run all of the code (including the function SearchAndFind that doesn't work), the pop-up box comes up with undefined, linking nowhere. Admittedly, I don't have a lot of experience with Javascript so I think I just don't understand it well enough to find the bug here.

Akshin Jalilov Akshin Jalilov · Accepted Answer · 2015-06-10T05:58:29

You are pulling only one column and then checking for the match in columns.

var range = sh.getRange(1, 1, sh.getMaxRows(), 1);

Gives you only the first column. And

for (n = 0; n < values[0].length; n++){

Looks through those columns. So your values[0].length is 1, and your loop only runs once.

Are you trying to loop through all rows form first column, or through first row of all columns.

Also you should change your loop to

for (var n = 0; n < values[0].length; n++){

Javascript on Google Sheets - Get Column Number from Prompt Input

1 Answers