Disclaimer I a newbie at programin and also not really good at english, so please pardon my bad explanation.

I have this code:

function SearchAttributebyName(ID){
  var SheetDatabase = SpreadsheetApp.openById("SS ID");
  var Sheet = SheetDatabase.getSheetByName("Sheet1");
  var Cell = Sheet.getRange("A3:A");
  var Value = Cell.getValues().map(x => x[0]);
  Logger.log(Value.length);

  Value.forEach((elements, i) => {
    if (elements == ID) {
      let baris = i   3;
      let name = Sheet.getRange(baris,1,1,1).getValues();
      let A= Sheet.getRange(baris,15,1,1).getValues();
      let B= Sheet.getRange(baris,16,1,1).getValues();
      let C= Sheet.getRange(baris,17,1,1).getValues();
      let D= Sheet.getRange(baris,4,1,1).getValues();
      let E= Sheet.getRange(baris,21,1,1).getValues();
      let F= Sheet.getRange(baris,22,1,1).getValues();
      let G= Sheet.getRange(baris,11,1,1).getValues();
      let H= Sheet.getRange(baris,12,1,1).getValues();
      let I= Sheet.getRange(baris,7,1,1).getValues();
      let J= Sheet.getRange(baris,8,1,1).getValues();
      let K= Sheet.getRange(baris,9,1,1).getValues();
      let L= Sheet.getRange(baris,27,1,1).getValues();
      let M= Sheet.getRange(baris,5,1,1).getValues();
      
      return "✅ DATA OF "   name   ": "   "\n"   "\n"  
             "DataA : "   A  "\n"  
             "DataB : "   B   "\n"  
             "DataC : "   C   "\n"  
             "DataD : "   D   "\n"  
             "DataE : "   E   "\n"  
             "DataF : "   F   "\n"  
             "DataG : "   G   "\n"  
             "DataH : "   H   "\n"  
             "DataI: "   I   "\n"  
             "DataJ : "   J   "\n"  
             "DataK : "   K   "\n"  
             "DataL : "   L   "\n"  
             "DataM : "   M;
    }
    return "DATA NOT FOUND";
  });
}

function trythebot() {
  Logger.log(SearchAttributebyODPName("XXXXXXX"));
}

This works fine when I check the SearchAttributebyName function with Logger.log. But when I check the SearchAttributebyName function with trythebot function, i keep getting null output. please help.

CodePudding user response：

You need a small change, SearchAttributebyName does not actually return anything because the forEach loop itself does not have a return statement.

Here's a possible approach

function SearchAttributebyName(ID) {
    var SheetDatabase = SpreadsheetApp.openById("SS ID");
    var Sheet = SheetDatabase.getSheetByName("Sheet1");
    var Cell = Sheet.getRange("A3:A");
    var Value = Cell.getValues().map((x) => x[0]);
    Logger.log(Value.length);

    let result;
    Value.forEach((elements, i) => {
        if (elements == ID) {
            let baris = i   3;
            let name = Sheet.getRange(baris, 1, 1, 1).getValues();
            let A = Sheet.getRange(baris, 15, 1, 1).getValues();
            let B = Sheet.getRange(baris, 16, 1, 1).getValues();
            let C = Sheet.getRange(baris, 17, 1, 1).getValues();
            let D = Sheet.getRange(baris, 4, 1, 1).getValues();
            let E = Sheet.getRange(baris, 21, 1, 1).getValues();
            let F = Sheet.getRange(baris, 22, 1, 1).getValues();
            let G = Sheet.getRange(baris, 11, 1, 1).getValues();
            let H = Sheet.getRange(baris, 12, 1, 1).getValues();
            let I = Sheet.getRange(baris, 7, 1, 1).getValues();
            let J = Sheet.getRange(baris, 8, 1, 1).getValues();
            let K = Sheet.getRange(baris, 9, 1, 1).getValues();
            let L = Sheet.getRange(baris, 27, 1, 1).getValues();
            let M = Sheet.getRange(baris, 5, 1, 1).getValues();

            result =  (
                "✅ DATA OF "  
                name  
                ": "  
                "\n"  
                "\n"  
                "DataA : "  
                A  
                "\n"  
                "DataB : "  
                B  
                "\n"  
                "DataC : "  
                C  
                "\n"  
                "DataD : "  
                D  
                "\n"  
                "DataE : "  
                E  
                "\n"  
                "DataF : "  
                F  
                "\n"  
                "DataG : "  
                G  
                "\n"  
                "DataH : "  
                H  
                "\n"  
                "DataI: "  
                I  
                "\n"  
                "DataJ : "  
                J  
                "\n"  
                "DataK : "  
                K  
                "\n"  
                "DataL : "  
                L  
                "\n"  
                "DataM : "  
                M
            );
        }
    });

    return result || "DATA NOT FOUND";
}

CodePudding user response：

The Array.forEach() method takes a function and runs that function for every element in the array you are iterating. The return statement in the function simply lets .forEach() proceed to the next element. The return value is not passed anywhere.

You can make the code work by using Array.map() instead, like this:

function test() {
  console.log(searchAttributebyName('XXXXXXX'));
}

function searchAttributebyName(name) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const indices = [1, 15, 16, 17, 4, 21, 22, 11, 12, 7, 8, 9, 27, 5]
    .map(index => index - 1);
  return sheet.getRange('A3:AA').getValues()
    .filter(row => row[0] === name)
    .map(row => reorderRow_(row, indices))
    .map(row => row.map((value, index) => `Data${index   1}: ${value}`))
    .map(row => row.join('\n'))
    .join('\n\n') || `(no row matches ${name})`;
}


/**
* Returns a copy of a 1D array with values rearranged by indices
* and sliced to indices.length.
*
* @param {Object[]} row The row to rearrange.
* @param {Number[]} indices The indices to use when the 
* @return {Object[]} The rearranged row with length adjusted to indices.length.
*/
function reorderRow_(row, indices) {
  // version 1.0, written by --Hyde, 23 January 2022
  //  - see https://stackoverflow.com/q/75206328/13045193
  return indices.map(index => row[index]);
}