Search code examples
google-sheets

Google Sheet: Import data based on header name but header is sometimes shifted to nth row

I have a spreadsheet where 10+ users are working on. These users will sometimes add new rows above the header (Client, Device, Issue) and sometimes add/remove columns around. I am trying to combine sheet1 and sheet2 into a combined sheet.

I have this formula below that would help me find a column based on header name but the issue is that the formula only works if the header is in row 1:

=LET(data,importrange("1wnn-I_8FkvJcVa7EQhTj8lKDxxxTCXcoG7pBuIihOUk/edit#gid=0", "sheet1_apple_devices!1:1000"),FILTER(data,IFNA(XMATCH(INDEX(data,1),{"Client"}))))

As stated, sometimes people working on the spreadsheet will move the header to nth row and I want the formula to work regardless which row/column the header is on. Greatly appreciated, thank you so much.

Solution

  • Alternate Option / Workaround

    Please note that this answer serves as a reference only & it is important to note that Stackoverflow members do not provide coding services.

    As I understand, you are trying to import and combine two different sheets into a single sheet & make it dynamically adjust regardless of changes on each sheet rows before the header. This can be tricky when using the standard Google Sheets functions.

    As an alternate method, you can try and test this sample custom Google Sheets function below using Google Apps Script.

    Sample Custom Function:
    =combine(importrange("1wnn-I_8FkvJcVa7EQhTj8lKDxxxTCXcoG7pBuIihOUk/edit#gid=0","A:C"),importrange("1wnn-I_8FkvJcVa7EQhTj8lKDxxxTCXcoG7pBuIihOUk/edit#gid=0","A:C"),"Client")

    Custom Function Script

    /**
 * Combines two different sheets into one piece of data.
 * @param {arr} input The importrange of the first sheet.
 * @param {arr} input The importrange of the second sheet.
 * @param {string} input The header name to find the "header name" to be based on the result.
 * @return the combined sheets data.
 * @customfunction
*/
function combine(range1, range2, find) {
  var found; //Temporarily store the index number where "Client" or the value of the "find" parameter was found.
  var clean; //Serves as the basis to not include all data before the header.

  const generateSheet2 = () => {
    //Find and store the non-header data to be 'cleaned'.
    range2.forEach((x, i) => x.toString().match(find) ? clean = i : null);
    var res = [];
    var sheet2 = range2.filter((x, i) => x.toString().length != 2 && i >= clean).map(y => y.map((z, i) => z == find ? found = i : z))
    sheet2.forEach(d => res.push(d[found]));
    return res.map((r, i) => [r, sheet2[i].filter((_, i) => i != found)].flat()).filter((_, i) => i != 0);
  }

  const generateSheet1 = () => {
    //Find and store the non-header data to be 'cleaned'.
    range1.forEach((x, i) => x.toString().match(find) ? clean = i : null);
    var res = [];
    var sheet1 = range1.filter((x, i) => x.toString().length != 2 && i >= clean).map(y => y.map((z, i) => z == find ? found = i : z))
    sheet1.forEach(d => res.push(d[found]));
    return res.map((r, i) => [r, sheet1[i].filter((_, i) => i != found)].flat()).filter((_, i) => i != 0);
  }

  return generateSheet1().concat(generateSheet2());

}

    How to use?

    Prerequisites: Add the script into your sheet and save it.

    1. Since this matter involves importrange() to feed the combine() custom function with data, you have to allow the Permission & access on each importrange() functions (one-time only) by clicking Allow access button. Otherwise, you'll encounter an error:

    enter image description here

    1. Use the combine function into your destination sheet and define the parameters needed:

    enter image description here

    • range1 will be your first importrange().

      E.g. importrange("1wnn-I_8FkvJcVa7EQhTj8lKDxxxTCXcoG7pBuIihOUk/edit#gid=0", "A:C").

    • range2 will be your second importrange().

      E.g. importrange("1wnn-I_8FkvJcVa7EQhTj8lKDxxxTCXcoG7pBuIihOUk/edit#gid=0", "A:C").

    • find will be the parameter to help you find the column header name as basis on the final combined data (the first header data to be followed).

      E.g. "Client".

    1. Once added, the custom function will combine the two sheets into one where both sheet first columns will be based on the "Client" header and clean unnecessary rows of data before the header.

    enter image description here

    Reference