Import data from another spreadsheet & organizing under similar headers

Question

I'm setting up a main spreadsheet to try and organize data from multiple different spreadsheets. Is it possible to import the data from the different spreadsheets and have the data automatically organize according to similarities between the main and secondary spreadsheet's headers?

For example, my main spreadsheet & secondary spreadsheet both have "Domain", "Region", and "Language" headers, but the secondary doesn't have "Status", "Contact" fields. When I import, I want Sheets to look at the "Domain" field from both and populate in the correct column, but ignore the missing "Status" field.

Right now, using Sheet's IMPORTRANGE function, as well as import tool, all I'm getting is a simple copy and paste where it just puts the imported data in the same format / organization without regard to the target (main) spreadsheet's data schema.

Hi ! What tool or tools are you aiming for achieving this purpose ? Apps Script ? Just Google Sheet formulas? Or Sheets API ? Also, could you share a sample sheet or a picture with your actual and desired output with no sensitive information to provide a bit of clarification on your question ? Thanks ! :D — Mateo Randwolf

Jeff Gibson Jeff Gibson · Accepted Answer · 2020-10-01T15:25:55

Seems this could be a simple query, unless I'm misunderstanding the question.

Sheet1 with 3 columns A,B,C Sheet2 with 5 columns A,B,C,D,E

A QUERY in Sheet 1 would populate the columns you want. For example:

=query(Sheet2!A1:C,"Select A,B,C",1)

would pull just the first 3 columns in each row of the data.

Import data from another spreadsheet & organizing under similar headers

1 Answers