CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Ramy CoreMT
Ramy CoreMT

Reputation: 83

Retrieving a full row of google sheets data to JavaScript

I am trying to build a form that can recall data based on a specific number being inputted using Google Scripts and JavaScript. When a number is inputted the JavaScript should call the Google Scripts so that it can locate the values I need based on the number. For example bellow is a google sheets. I want when the user inputs a number it searches for that value in column C and then grabs the data from that row. Ex. if the number is 14 then values: 2021-05-12, 5, 6 and 7 are returned to the JavaScript.

UPDATED: enter image description here

Everything I want is working however, when I try to retrieve the date from the array in the JavaScript it does not work. The numbers are the only thing functioning. I know the date is in the array as I can see it in the logs.

JavaScript:

  function callDataRetriever(){
    var number = document.getElementById("number").value;
    google.script.run.withSuccessHandler(dataRetriever).retreiveData(number);
  }

  function dataRetriever(data){
    document.getElementById("location").value = data[0]; //This works
    document.getElementById("dateOpened").value = data[1]; //This does not work. Stops the function from continuing its task.        
    document.getElementById("value1").value = data[2]; //Without the date everything here down works
    document.getElementById("value2").value = data[3];
    document.getElementById("value2").value = data[4];
    document.getElementById("value4").value = data[5];
    //...
  }

Google Scripts (I have 28 total values) Everything here works perfectly as seen in the logs bellow.

function retreiveData(number){
  var url = "urlToSpreadsheet";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var data = ws.getRange(1,1, ws.getLastRow(), ws.getLastColumn()).getValues();
  var dataValues = [];
  var filterData = data.filter(
    function(r){
      if(r[2] == number){
        var i = 3;
        while(i < 29){
          dataValues.push(r[i]);  
          i++;
        }
      }
    }
  )
  return dataValues;
}

enter image description here

I am not sure if the problem lies in the way the date is formatted.

Thank you!

Upvotes: 0

Views: 979

Answers (3)

Tanaike
Tanaike

Reputation: 201428

I believe your goal as follows.

  • When number is given and run the function retreiveData, you want to search the value of number from the cells "C4:C" of "Data" sheet, and want to retrieve the values of the columns "D", "E" and "F" for the searched row.

In this case, I would like to propose to use TextFinder. When the TextFinder is used, searching value is run in the internal server side. By this, the process cost will be a bit low. Ref

When this is reflected to your script, it becomes as follows.

Modified script:

In this case, please modify retreiveData at Google Apps Script side as follows.

function retreiveData(number){
  var url = "linkToSpreadsheet";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var data = ws.getRange("C4:C" + ws.getLastRow()).createTextFinder(number).matchEntireCell(true).findNext();
  if (data) {
    return data.offset(0, 1, 1, 4).getValues()[0];
  }
  return Array(4).fill("No value");
}

And, please modify dataRetriever at Javascript side as follows.

function dataRetriever(data){
  console.log(data) // Here, you can check the value from `retreiveData` at the console.
  data.forEach((v, i) => {
    document.getElementById(`value${i + 1}`).value = v;
  });
}

Note:

  • In this case, when the search value of number is not found, No value is put to the input tags of value1 to value4. When you want to modify this, please modify return Array(4).fill("No value"); for your actual situation.

References:

Upvotes: 2

Yuri Khristich
Yuri Khristich

Reputation: 14537

There are two ways:

var data = [
    [1,  55, 5545,   54,   51],
    [2,  45,  541,  848, 1215],
    [3, 323, 3232,  215, 3051],
    [4,  13, 5151, 1513, 2315]
]

number = 3;


// via object where the 'numbers' are keys

var data_obj = Object.fromEntries(data.map(d => [d[0], d.slice(1,)]));
console.log(data_obj[number]); // [ 323, 3232, 215, 3051 ]


// via filter (about the same as @macorreag's answer)

var res = data.filter(d => d[0] == number)[0].slice(1,);
console.log(res); // [ 323, 3232, 215, 3051 ]

An object makes sense in the cases when you need to extract info from the same data several times. For single requests a filter looks better, I think.

And if the numbers always start from 1 and go sequentially 2, 3, 4, 5, ... etc, the simpliest solution is an array and its indexes:

var data = [
    [1,  55, 5545,   54,   51],
    [2,  45,  541,  848, 1215],
    [3, 323, 3232,  215, 3051],
    [4,  13, 5151, 1513, 2315]
]

var number = 3;

var data_array = data.map(d => d.slice(1,));
console.log(data_array[number-1]); // [ 323, 3232, 215, 3051 ]

In this case you don't even need the first column 'numbers' at all. You can just extract the next four columns and get the rows by indexes (-1): number 1 is data[0], number 2 is data[1] etc.

Upvotes: 1

macorreag
macorreag

Reputation: 372

You can use filter to perform the operation you want

var filterData = data.filter(
  function(r){
    //Select the index init in 0 in your case is 2
    return r[0] == 'YOUR_SEARCH_VALUE'
  }
)    
// Use your filter data
ui.alert(filterData[0]) //data[row][column]

Upvotes: 2

Related Questions