0

Example sheet to clarify my problem: https://docs.google.com/spreadsheets/d/1e04XmYkirDJCucCv5Z1czCtg2OQkJfWyThjhF3uz8FQ/edit?usp=sharing

I am fetching json data from a server, however on some fetches there is a column of data missing or added extra data, which doesn't allow me to just easily select the column of data that I would like to work further on.

Sample

sample data

In the example above, I would like to search each row of json data for historic and current value and have it separate from the json data, so that I can easily reference it for further use in the spreadsheet. Basically, how can I search each row for a key term(s) and return that associated value in a separate part of the spreadsheet?

Improve this question
2
  • 1
    Welcome to Web Applications. While a link to an external resource might be helpful, questions should be self contained, so please add more details and clarify what you are actually asking, (are you looking for help with a Google Sheets built-in feature or with using Google Apps Script?)
    – Rubén - Volunteer Moderator - ♦
    Commented Feb 19, 2022 at 19:19
  • Your sample spreadsheet also does not contain realistic data or the hand-entered results you want a formula to produce. For instance, I see Row 2 contains "current value #1" in cells E2 and I2; but what does that look like in real life. It is certain that the actual values won't be the string "current value #1"; it is uncertain, however, what that data might really look like. Will it follow some predictable pattern by which it can be identified? Will the same exact value show up multiple times in a row as you have show? Short version: change your sample data to be realistic.
    – Erik Tyler
    Commented Feb 20, 2022 at 17:08

1 Answer 1

Reset to default
2

Enter this formula in cell I1:

={query({flatten(A1:G)},"select Col1 where Col1 contains 'current'"),query({flatten(A1:G)},"select Col1 where Col1 contains 'historic'")}

Result Output

Logic

  • flatten(A1:G) - takes the entire data source and reduces it to a single column of data
  • "select Col1 where Col1 contains 'current'" - returns the values containing the word "current"
  • "select Col1 where Col1 contains 'historic'"- returns the values containing the word "historic"
  • {query(),query()} - creates an array; use of the "comma" arranges the results side-by side.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.