Simple Formula to Map Imported Data to Core Sheets

Google Sheets Show & Tell
1

Overview

As a member of the Tiller development team, I spend a lot of time testing new templates and workflows. I often need to pull transaction and balance history datasets into test sheets and a tiresome speedbump is matching the column order of the source and destination spreadsheets when appending data.

Rather than groaning every time I confront this problem (daily! :frowning_face:), I decided to make a simple helper sheet to remap the data for me. The helper sheet— which I’m calling “Remapped Transactions”— will mirror the column order of your Transactions sheet, but automatically fill properly-mapped data from the imported sheet— which I’m calling “Imported Transactions”.

Though this workflow is most useful for me for development purposes, I believe this template could broadly help users performing migrations and data imports. For example, with some customizations to implement column-header remapping the core concept could be extended into a robust CSV import tool.

Installation

  1. Insert a new sheet into to your spreadsheet
  2. Rename the new sheet “Remapped Transactions”
  3. Copy the formula below into cell A1
  4. Expand/copy the formula across the rest of the header row (B1 and beyond)

Formula

The formula isn’t super complicated. Essentially we just create a static array to pull the headers (in their original column order) from the Transactions sheet, then use an OFFSET() with a MATCH() lookup to search for and pull data from the correct column in the imported data sheet. An IFERROR() function just cleans up any possible messes.

={
  Transactions!A$1;
  iferror(
    if(
      istext(Transactions!A$1),
      offset(
        INDIRECT("'Imported Transactions'!$A$2:$A"),
        0,
        MATCH(Transactions!A$1,INDIRECT("'Imported Transactions'!$A$1:$1"),0)-1),
      1/0
    )
  )
}

Usage

  1. Use the sheet-copy tooling in Sheets to copy source data out of a spreadsheet into your spreadsheet containing Remapped Transactions.
  2. Rename the imported sheet “Imported Transactions”.
  3. If the data doesn’t fill, you may need to jostle the formula in the header row of “Remapped Transactions” to get the INDIRECT() to recalc with the newly-present sheet name— do something like adding a space at the end of the formula in A1 and then expanding it across the header row again.
  4. Copy the remapped data from Remapped Transactions and paste as values into your Transactions sheet.

Permissions

Feel free to use, copy or improve on this.

Notes

  • With some small adjustments this could work for Balance History data too.