0

I am importing data with ImportJSON from Yahoo Finance of different stocks. It is the first table at the top, that's showing some data about different parameters.

The problem is that due to some paths have several rows of data, the table has many rows.

I want to have a unique row taking the first value of each column, like in the middle's table. I can't use several ImportJSON due to Url Fetch Calls limits because it will be done with several stocks.

I don't know how to start. I would be glad if someone can resolve it.

I need to resolve with one formula or with some code both ImportJSON() and the way to get the final result, without any helpers cells.

The bottom table is the result I want to have after the formula / script is done.

Watch out not all stocks have the same amount of rows.

enter image description here

I've left you the spreadsheet.

Spreadsheet

Thanks in advance.

Antor Cha
  • 73
  • 9

1 Answers1

1

As I am not that expert in conjuring complicated formulas, I have made a custom formula in Google Apps Script instead. The parameter is the range E2:U8, but it can be any range.

Code:

function FINDNONBLANK(table) {
  var result = [[]];
  for (i=0;i<table[0].length;i++) {
    for (j=0;j<table.length;j++) {
      if (table[j][i]!="") {
        result[0].push(table[j][i]);
        j = table.length;
      }
    }
  }
  return result;
}

Sample Result:

enter image description here

CMB
  • 4,950
  • 1
  • 4
  • 16
  • Thanks, that's great!. This is exactly what I want, but I need to add the ImportJSON to that code due I won't have that space for each stock. If it can be done, how could I apply this code for a list of stocks? For example, having a list of stocks in Col A and apply it automatically. – Antor Cha Feb 26 '21 at 15:59
  • This should answer your follow-up question: https://stackoverflow.com/questions/26805309/how-to-evaluate-a-spreadsheet-formula-within-a-custom-function, note that you still need to store the importJSON result in helper cells. – CMB Feb 26 '21 at 22:22
  • Thanks, so I will go with that. Is there any way to leave blank columns blank in the result, not removing them? Check out the spreadsheet, with BABA ticker. – Antor Cha Feb 27 '21 at 07:26