Track Owner Export > HubSpot Import Process

 

Export Process From Track

1. Log in to the respective Track instance.
2. On the left-hand side menu, hover over the second-to-last icon and click on Reports.
3. Under the Owner section, click on Units by Owner List.
4. In Unit Status, select Both. Under Locations, select AMI Locals, AMI Accommodations, and Anna Maria Vacations.
5. Click Export as Excel.

Manual Spreadsheet Prepping for Import

Step 1: Consolidate Duplicate Emails

This step combines all properties for each owner into a single row.

A. Identify Duplicates

1. Sort the Email column using the Filter option and select Sort by A to Z.

Now all duplicate emails are grouped, and you can see how many times each email appears.

B. Consolidate Company Names

For each group of duplicate emails, you'll keep the first row and combine data from the others:

1. Find the first duplicate email group.
2. Look at all the Company values for that email.
3. In the first row's Company cell:
   • Click in the cell.
   • Type or paste the next company name.
   • Press Alt/Ctrl+Enter (Windows) or Option+Enter (Mac) to create a line break.
   • Type or paste any other company names.
   • Repeat for all properties/units.

C. Consolidate Local Office/Brands (if different)

1. Look at the Local Office column for all rows with the same email.
2. If the brands are different across properties:
   • In each cell, combine them with semicolons.
     • Example: ;Anna Maria Vacations;AMI Locals;AMI Accommodations
   • Rename 'AMI Accommodations' to 'Anna Maria Island Accommodations', so it matches HubSpot terminology.
3. If the brands are the same, keep one.

D. Delete Duplicate Rows

After consolidating Company and Local Office for a group:

1. Select the duplicate rows (all rows except the first one for that email).
2. Right-click > Delete rows.
3. Move to the next email group and repeat.

Step 2: Delete Unnecessary Columns

Right-click each column header and select "Delete column" for the following:

AMI Columns

• Company Street Address
• Company Extended Address
• Company Locality
• Company Region
• Company Postal Code
• Company Postal
• Company Country
• Contact Extended Address
• Property Care Team
• Condo App
• PMC Pays Bills - FPL
• PMC Pays Bills - MCUD
• PMC Pays Bills - Cable
• PMC Pays Bills - Waste
• Property Success Team
• Property Success Manager
• Default Linen Provider
• Hotel Style Units
• Shawn Homes
• Roman Homes
• Exclusive AMI
• Fee Pricing Test
• Airbnb Personal Account

Siesta Key Columns

• Company Street Address
• Company Extended Address
• Company Locality
• Company Region
• Company Postal
• Company Country
• Contact Extended Address
• Property Success Manager
• Property Success Team
• Shawn Homes
• Roman Homes
• Condo App

Step 3: Rename Columns

Click on each remaining column header and rename as follows:

Step 4: Split Contact Names

1. Insert two new columns after the Contact column:
   • Right-click the column to the right of "Contact."
   • Select "Insert 1 column right."
   • Repeat to create a second column.
2. Name the new columns:
   • First new column: "First Name."
   • Second new column: "Last Name."
3. Split the names using a formula:
   • In the First Name column (row 2), enter:=IF(ISBLANK(C2),"",LEFT(C2,FIND(" ",C2&" ")-1))
   • In the Last Name column (row 2), enter: =IF(ISBLANK(C2),"",TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),100)))
   • Drag both formulas down to fill all rows
4. Double-check all first names and last names have been populated correctly.
5. Delete the original Contact column.

Step 5: Set Contact Type Values

1. Click on the Contact Type column header (formerly "Unit Name").
2. Select all data cells in that column (not the header).
3. Type Owner and press Ctrl+Enter (or Cmd+Enter on Mac) to fill all cells.

Step 6: Create Homeowner Type Column

1. Insert a new column next to the Is Active column:

   • Right-click the column to the right of "Is Active."
   • Select "Insert 1 column left."

2. Name the column: "Homeowner Type."

3. Add a formula in the first data row (row 2): =IF(ISBLANK([Is Active cell]),"",IF([Is Active cell]=1,"Prime Owner",IF([Is Active cell]=0,"Lapsed Owner","")))

      4. Drag the formula down to fill all rows.

      5. Double-check that all Is Active=1 columns are set to Prime Owner and Is Active=0             columns are set to Lapsed Owner.

      6. Delete the Is Active column.

Step 7: Double-check all State data

1. Check that all states use abbreviation and are not spelled out. If spelled out, make sure you change to the abbreviation.
2. If State shows and unrecognized value (e.g., US or other), delete the data in the field.

Step 8: Keep These Columns Unchanged

The following columns require no changes and should remain as-is:

• Email
• Secondary Email
• Home Phone (you can also rename to Phone Number)
• Mobile Phone

Step 9: Final Review

Before importing to HubSpot:

1. ✅ Verify all column names are correct.
2. ✅ Ensure all "Contact Type" cells say "Owner."
3. ✅ Confirm "Homeowner Type" shows either "Prime Owner" or "Lapsed Owner."
4. ✅ Verify First Name and Last Name are properly split.
5. ✅ Check that all unnecessary columns are deleted.
6. ✅ Check that all states use abbreviations.
7. ✅ Confirm semicolons are added in the Brands column.

---

Automated Spreadsheet Prep Process: Google Sheets App Script

To prevent having to edit each spreadsheet manually, follow the process detailed below and use the specified script to make all the changes above automatically.

1. Open the downloaded CSV doc in Google Sheets.
2. Rename the document as specified in the instructions above.
3. Click on Extensions > App Script
4. Copy the script below into the code field:

AMI Spreadsheet

function prepareForHubSpot() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  Logger.log('Starting processing...');
  
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('No data to process.');
    return;
  }
  
  // Get all data at once
  const allData = sheet.getRange(1, 1, lastRow, lastCol).getValues();
  const headers = allData[0];
  
  Logger.log('Data loaded. Rows: ' + lastRow);
  
  // Helper function to find column index
  function findCol(name) {
    return headers.indexOf(name);
  }
  
  // STEP 1: Skip the lapsed owner deletion - we'll consolidate all properties by email instead
  Logger.log('Skipping lapsed owner deletion - will consolidate all properties per email');
  
  // STEP 2: Get data for column operations
  const data2 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers2 = data2[0];
  
  function findCol2(name) {
    return headers2.indexOf(name);
  }
  
  // STEP 3: Delete unwanted columns (in reverse order)
  Logger.log('Deleting columns...');
  const columnsToDelete = [
    'Airbnb Personal Account', 'Fee Pricing Test', 'Exclusive AMI',
    'Roman Homes', 'Shawn Homes', 'Default Linen Provider', 'Hotel Style Units',
    'Property Success Manager', 'Property Success Team', 'PMC Pays Bills - Waste',
    'PMC Pays Bills - Cable', 'PMC Pays Bills - MCUD', 'PMC Pays Bills - FPL',
    'Condo App', 'Property Care Team', 'Contact Extended Address',
    'Company Country', 'Company Postal Code', 'Company Postal', 'Company Region',
    'Company Locality', 'Company Extended Address', 'Company Street Address'
  ];
  
  const deleteIndices = [];
  columnsToDelete.forEach(colName => {
    const idx = findCol2(colName);
    if (idx !== -1) deleteIndices.push(idx);
  });
  
  deleteIndices.sort((a, b) => b - a);
  deleteIndices.forEach(idx => {
    sheet.deleteColumn(idx + 1);
  });
  
  // STEP 4: Refresh data after column deletions
  const data3 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers3 = data3[0];
  
  function findCol3(name) {
    return headers3.indexOf(name);
  }
  
  // STEP 5: Rename columns
  Logger.log('Renaming columns...');
  const renameMap = {
    'Unit Name': 'Contact Type',
    'Company': 'Company Names',
    'Contact Street Address': 'Street Address',
    'Contact Locality': 'City',
    'Contact Region': 'State',
    'Contact Postal Code': 'Zip Code',
    'Contact Postal': 'Zip Code',
    'Contact Country': 'Country',
    'Local Office': 'Brands'
  };
  
  Object.keys(renameMap).forEach(oldName => {
    const idx = findCol3(oldName);
    if (idx !== -1) {
      sheet.getRange(1, idx + 1).setValue(renameMap[oldName]);
    }
  });
  
  // STEP 5B: Convert state names to abbreviations
  Logger.log('Converting states to abbreviations...');
  const data3b = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers3b = data3b[0];
  
  function findCol3b(name) {
    return headers3b.indexOf(name);
  }
  
  const stateIdx = findCol3b('State');
  if (stateIdx !== -1) {
    const stateMap = {
      'alabama': 'AL', 'alaska': 'AK', 'arizona': 'AZ', 'arkansas': 'AR', 'california': 'CA',
      'colorado': 'CO', 'connecticut': 'CT', 'delaware': 'DE', 'florida': 'FL', 'georgia': 'GA',
      'hawaii': 'HI', 'idaho': 'ID', 'illinois': 'IL', 'indiana': 'IN', 'iowa': 'IA',
      'kansas': 'KS', 'kentucky': 'KY', 'louisiana': 'LA', 'maine': 'ME', 'maryland': 'MD',
      'massachusetts': 'MA', 'michigan': 'MI', 'minnesota': 'MN', 'mississippi': 'MS', 'missouri': 'MO',
      'montana': 'MT', 'nebraska': 'NE', 'nevada': 'NV', 'new hampshire': 'NH', 'new jersey': 'NJ',
      'new mexico': 'NM', 'new york': 'NY', 'north carolina': 'NC', 'north dakota': 'ND', 'ohio': 'OH',
      'oklahoma': 'OK', 'oregon': 'OR', 'pennsylvania': 'PA', 'rhode island': 'RI', 'south carolina': 'SC',
      'south dakota': 'SD', 'tennessee': 'TN', 'texas': 'TX', 'utah': 'UT', 'vermont': 'VT',
      'virginia': 'VA', 'washington': 'WA', 'west virginia': 'WV', 'wisconsin': 'WI', 'wyoming': 'WY'
    };
    
    const stateData = [];
    for (let i = 1; i < data3b.length; i++) {
      const state = data3b[i][stateIdx];
      if (state) {
        const stateLower = state.toString().trim().toLowerCase();
        stateData.push([stateMap[stateLower] || state]); // Use abbreviation if found, otherwise keep original
      } else {
        stateData.push([state]);
      }
    }
    
    if (stateData.length > 0) {
      sheet.getRange(2, stateIdx + 1, stateData.length, 1).setValues(stateData);
    }
  }
  
  // STEP 6: Split Contact into First Name and Last Name
  Logger.log('Splitting names...');
  const data4 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers4 = data4[0];
  
  function findCol4(name) {
    return headers4.indexOf(name);
  }
  
  const contactIdx = findCol4('Contact');
  if (contactIdx !== -1) {
    sheet.insertColumnsAfter(contactIdx + 1, 2);
    sheet.getRange(1, contactIdx + 2).setValue('First Name');
    sheet.getRange(1, contactIdx + 3).setValue('Last Name');
    
    const nameData = [];
    for (let i = 1; i < data4.length; i++) {
      const fullName = data4[i][contactIdx];
      if (fullName) {
        const nameParts = fullName.toString().trim().split(' ');
        nameData.push([nameParts[0] || '', nameParts.slice(1).join(' ') || '']);
      } else {
        nameData.push(['', '']);
      }
    }
    
    if (nameData.length > 0) {
      sheet.getRange(2, contactIdx + 2, nameData.length, 2).setValues(nameData);
    }
    
    sheet.deleteColumn(contactIdx + 1);
  }
  
  // STEP 7: Add Homeowner Type column
  Logger.log('Adding Homeowner Type...');
  const data5 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers5 = data5[0];
  
  function findCol5(name) {
    return headers5.indexOf(name);
  }
  
  const isActiveIdx5 = findCol5('Is Active');
  const existingHomeownerType = findCol5('Homeowner Type');
  
  // Only create column if it doesn't already exist
  if (isActiveIdx5 !== -1 && existingHomeownerType === -1) {
    sheet.insertColumnAfter(isActiveIdx5 + 1);
    sheet.getRange(1, isActiveIdx5 + 2).setValue('Homeowner Type');
    
    const typeData = [];
    for (let i = 1; i < data5.length; i++) {
      const isActive = data5[i][isActiveIdx5];
      typeData.push([isActive == 1 ? 'Prime Owner' : isActive == 0 ? 'Lapsed Owner' : '']);
    }
    
    if (typeData.length > 0) {
      sheet.getRange(2, isActiveIdx5 + 2, typeData.length, 1).setValues(typeData);
    }
  }
  
  // STEP 8: Set Contact Type to "Owner"
  Logger.log('Setting Contact Type...');
  const data6 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers6 = data6[0];
  
  function findCol6(name) {
    return headers6.indexOf(name);
  }
  
  const contactTypeIdx = findCol6('Contact Type');
  if (contactTypeIdx !== -1) {
    const ownerData = Array(data6.length - 1).fill(['Owner']);
    if (ownerData.length > 0) {
      sheet.getRange(2, contactTypeIdx + 1, ownerData.length, 1).setValues(ownerData);
    }
  }
  
  // STEP 9: Consolidate rows by email and combine Company Names
  Logger.log('Consolidating duplicate emails...');
  const finalData = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const finalHeaders = finalData[0];
  
  function findFinalCol(name) {
    return finalHeaders.indexOf(name);
  }
  
  const finalEmailIdx = findFinalCol('Email');
  const companyNameIdx = findFinalCol('Company Names');
  const brandsIdx = findFinalCol('Brands');
  const isActiveIdx = findFinalCol('Is Active');
  const homeownerTypeIdx = findFinalCol('Homeowner Type');
  
  let consolidatedCount = 0;
  const originalRowCount = finalData.length - 1;
  
  if (finalEmailIdx !== -1 && companyNameIdx !== -1) {
    // Build a map of email -> consolidated row data
    const emailMap = {};
    
    for (let i = 1; i < finalData.length; i++) {
      const email = finalData[i][finalEmailIdx];
      if (!email) continue;
      
      const emailKey = email.toString().trim().toLowerCase();
      const isActive = finalData[i][isActiveIdx];
      
      if (!emailMap[emailKey]) {
        // First occurrence - store the entire row
        emailMap[emailKey] = {
          rowData: [...finalData[i]],
          companyNames: [finalData[i][companyNameIdx]],
          brands: [],
          hasActiveProperty: isActive == 1,
          hasLapsedProperty: isActive == 0
        };
        
        // Add brand if it exists
        const brand = finalData[i][brandsIdx];
        if (brand) {
          const brandStr = brand.toString().trim();
          // Rename AMI Accommodations to Anna Maria Island Accommodations
          const normalizedBrand = brandStr === 'AMI Accommodations' ? 'Anna Maria Island Accommodations' : brandStr;
          emailMap[emailKey].brands.push(normalizedBrand);
        }
      } else {
        // Duplicate email - add the company name
        const companyName = finalData[i][companyNameIdx];
        if (companyName && !emailMap[emailKey].companyNames.includes(companyName)) {
          emailMap[emailKey].companyNames.push(companyName);
        }
        
        // Add brand if it exists and isn't already in the list
        const brand = finalData[i][brandsIdx];
        if (brand) {
          const brandStr = brand.toString().trim();
          // Rename AMI Accommodations to Anna Maria Island Accommodations
          const normalizedBrand = brandStr === 'AMI Accommodations' ? 'Anna Maria Island Accommodations' : brandStr;
          if (!emailMap[emailKey].brands.includes(normalizedBrand)) {
            emailMap[emailKey].brands.push(normalizedBrand);
          }
        }
        
        // Track if this owner has both active and lapsed properties
        if (isActive == 1) emailMap[emailKey].hasActiveProperty = true;
        if (isActive == 0) emailMap[emailKey].hasLapsedProperty = true;
      }
    }
    
    // Clear all data rows (keep header)
    if (finalData.length > 1) {
      sheet.deleteRows(2, finalData.length - 1);
    }
    
    // Build consolidated data
    const consolidatedData = [];
    Object.keys(emailMap).forEach(emailKey => {
      const entry = emailMap[emailKey];
      const rowData = entry.rowData;
      
      // Combine company names with line breaks
      rowData[companyNameIdx] = entry.companyNames.join('\n');
      
      // Combine brands with semicolons (for HubSpot multi-select)
      // Add leading semicolon for HubSpot appending
      if (brandsIdx !== -1 && entry.brands.length > 0) {
        rowData[brandsIdx] = ';' + entry.brands.join(';');
      }
      
      // Set Homeowner Type based on whether they have active properties
      if (homeownerTypeIdx !== -1) {
        if (entry.hasActiveProperty) {
          rowData[homeownerTypeIdx] = 'Prime Owner';
          rowData[isActiveIdx] = 1; // Set Is Active to 1
        } else if (entry.hasLapsedProperty) {
          rowData[homeownerTypeIdx] = 'Lapsed Owner';
          rowData[isActiveIdx] = 0; // Set Is Active to 0
        }
      }
      
      consolidatedData.push(rowData);
    });
    
    // Write consolidated data back
    if (consolidatedData.length > 0) {
      sheet.getRange(2, 1, consolidatedData.length, consolidatedData[0].length).setValues(consolidatedData);
    }
    
    consolidatedCount = consolidatedData.length;
    Logger.log('Consolidated ' + originalRowCount + ' rows into ' + consolidatedCount + ' unique contacts');
  }
  
  Logger.log('Complete!');
  SpreadsheetApp.getUi().alert('HubSpot preparation complete!\n\nRows consolidated: ' + originalRowCount + ' → ' + consolidatedCount);
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('HubSpot Prep')
    .addItem('Prepare for HubSpot', 'prepareForHubSpot')
    .addToUi();
}

 

Siesta Key Spreadsheet

function prepareForHubSpot() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  Logger.log('Starting processing...');
  
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('No data to process.');
    return;
  }
  
  // Get all data at once
  const allData = sheet.getRange(1, 1, lastRow, lastCol).getValues();
  const headers = allData[0];
  
  Logger.log('Data loaded. Rows: ' + lastRow);
  
  // Helper function to find column index
  function findCol(name) {
    return headers.indexOf(name);
  }
  
  // STEP 1: Skip the lapsed owner deletion - we'll consolidate all properties by email instead
  Logger.log('Skipping lapsed owner deletion - will consolidate all properties per email');
  
  // STEP 2: Get data for column operations
  const data2 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers2 = data2[0];
  
  function findCol2(name) {
    return headers2.indexOf(name);
  }
  
  // STEP 3: Delete unwanted columns (in reverse order)
  Logger.log('Deleting columns...');
  const columnsToDelete = [
    'Condo App',
    'Roman Homes',
    'Shawn Homes',
    'Property Success Team',
    'Property Success Manager',
    'Contact Extended Address',
    'Company Country',
    'Company Postal',
    'Company Region',
    'Company Locality',
    'Company Extended Address',
    'Company Street Address'
  ];
  
  const deleteIndices = [];
  columnsToDelete.forEach(colName => {
    const idx = findCol2(colName);
    if (idx !== -1) deleteIndices.push(idx);
  });
  
  deleteIndices.sort((a, b) => b - a);
  deleteIndices.forEach(idx => {
    sheet.deleteColumn(idx + 1);
  });
  
  // STEP 4: Refresh data after column deletions
  const data3 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers3 = data3[0];
  
  function findCol3(name) {
    return headers3.indexOf(name);
  }
  
  // STEP 5: Rename columns
  Logger.log('Renaming columns...');
  const renameMap = {
    'Unit Name': 'Contact Type',
    'Company': 'Company Names',
    'Contact Street Address': 'Street Address',
    'Contact Locality': 'City',
    'Contact Region': 'State',
    'Contact Postal Code': 'Zip Code',
    'Contact Postal': 'Zip Code',
    'Contact Country': 'Country',
    'Local Office': 'Brands'
  };
  
  Object.keys(renameMap).forEach(oldName => {
    const idx = findCol3(oldName);
    if (idx !== -1) {
      sheet.getRange(1, idx + 1).setValue(renameMap[oldName]);
    }
  });
  
  // STEP 5B: Convert state names to abbreviations
  Logger.log('Converting states to abbreviations...');
  const data3b = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers3b = data3b[0];
  
  function findCol3b(name) {
    return headers3b.indexOf(name);
  }
  
  const stateIdx = findCol3b('State');
  if (stateIdx !== -1) {
    const stateMap = {
      'alabama': 'AL', 'alaska': 'AK', 'arizona': 'AZ', 'arkansas': 'AR', 'california': 'CA',
      'colorado': 'CO', 'connecticut': 'CT', 'delaware': 'DE', 'florida': 'FL', 'georgia': 'GA',
      'hawaii': 'HI', 'idaho': 'ID', 'illinois': 'IL', 'indiana': 'IN', 'iowa': 'IA',
      'kansas': 'KS', 'kentucky': 'KY', 'louisiana': 'LA', 'maine': 'ME', 'maryland': 'MD',
      'massachusetts': 'MA', 'michigan': 'MI', 'minnesota': 'MN', 'mississippi': 'MS', 'missouri': 'MO',
      'montana': 'MT', 'nebraska': 'NE', 'nevada': 'NV', 'new hampshire': 'NH', 'new jersey': 'NJ',
      'new mexico': 'NM', 'new york': 'NY', 'north carolina': 'NC', 'north dakota': 'ND', 'ohio': 'OH',
      'oklahoma': 'OK', 'oregon': 'OR', 'pennsylvania': 'PA', 'rhode island': 'RI', 'south carolina': 'SC',
      'south dakota': 'SD', 'tennessee': 'TN', 'texas': 'TX', 'utah': 'UT', 'vermont': 'VT',
      'virginia': 'VA', 'washington': 'WA', 'west virginia': 'WV', 'wisconsin': 'WI', 'wyoming': 'WY'
    };
    
    const stateData = [];
    for (let i = 1; i < data3b.length; i++) {
      const state = data3b[i][stateIdx];
      if (state) {
        const stateLower = state.toString().trim().toLowerCase();
        stateData.push([stateMap[stateLower] || state]); // Use abbreviation if found, otherwise keep original
      } else {
        stateData.push([state]);
      }
    }
    
    if (stateData.length > 0) {
      sheet.getRange(2, stateIdx + 1, stateData.length, 1).setValues(stateData);
    }
  }
  
  // STEP 6: Split Contact into First Name and Last Name
  Logger.log('Splitting names...');
  const data4 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers4 = data4[0];
  
  function findCol4(name) {
    return headers4.indexOf(name);
  }
  
  const contactIdx = findCol4('Contact');
  if (contactIdx !== -1) {
    sheet.insertColumnsAfter(contactIdx + 1, 2);
    sheet.getRange(1, contactIdx + 2).setValue('First Name');
    sheet.getRange(1, contactIdx + 3).setValue('Last Name');
    
    const nameData = [];
    for (let i = 1; i < data4.length; i++) {
      const fullName = data4[i][contactIdx];
      if (fullName) {
        const nameParts = fullName.toString().trim().split(' ');
        nameData.push([nameParts[0] || '', nameParts.slice(1).join(' ') || '']);
      } else {
        nameData.push(['', '']);
      }
    }
    
    if (nameData.length > 0) {
      sheet.getRange(2, contactIdx + 2, nameData.length, 2).setValues(nameData);
    }
    
    sheet.deleteColumn(contactIdx + 1);
  }
  
  // STEP 7: Add Homeowner Type column
  Logger.log('Adding Homeowner Type...');
  const data5 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers5 = data5[0];
  
  function findCol5(name) {
    return headers5.indexOf(name);
  }
  
  const isActiveIdx5 = findCol5('Is Active');
  const existingHomeownerType = findCol5('Homeowner Type');
  
  // Only create column if it doesn't already exist
  if (isActiveIdx5 !== -1 && existingHomeownerType === -1) {
    sheet.insertColumnAfter(isActiveIdx5 + 1);
    sheet.getRange(1, isActiveIdx5 + 2).setValue('Homeowner Type');
    
    const typeData = [];
    for (let i = 1; i < data5.length; i++) {
      const isActive = data5[i][isActiveIdx5];
      typeData.push([isActive == 1 ? 'Prime Owner' : isActive == 0 ? 'Lapsed Owner' : '']);
    }
    
    if (typeData.length > 0) {
      sheet.getRange(2, isActiveIdx5 + 2, typeData.length, 1).setValues(typeData);
    }
  }
  
  // STEP 8: Set Contact Type to "Owner"
  Logger.log('Setting Contact Type...');
  const data6 = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const headers6 = data6[0];
  
  function findCol6(name) {
    return headers6.indexOf(name);
  }
  
  const contactTypeIdx = findCol6('Contact Type');
  if (contactTypeIdx !== -1) {
    const ownerData = Array(data6.length - 1).fill(['Owner']);
    if (ownerData.length > 0) {
      sheet.getRange(2, contactTypeIdx + 1, ownerData.length, 1).setValues(ownerData);
    }
  }
  
  // STEP 9: Consolidate rows by email and combine Company Names
  Logger.log('Consolidating duplicate emails...');
  const finalData = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const finalHeaders = finalData[0];
  
  function findFinalCol(name) {
    return finalHeaders.indexOf(name);
  }
  
  const finalEmailIdx = findFinalCol('Email');
  const companyNameIdx = findFinalCol('Company Names');
  const brandsIdx = findFinalCol('Brands');
  const isActiveIdx = findFinalCol('Is Active');
  const homeownerTypeIdx = findFinalCol('Homeowner Type');
  
  let consolidatedCount = 0;
  const originalRowCount = finalData.length - 1;
  
  if (finalEmailIdx !== -1 && companyNameIdx !== -1) {
    // Build a map of email -> consolidated row data
    const emailMap = {};
    
    for (let i = 1; i < finalData.length; i++) {
      const email = finalData[i][finalEmailIdx];
      if (!email) continue;
      
      const emailKey = email.toString().trim().toLowerCase();
      const isActive = finalData[i][isActiveIdx];
      
      if (!emailMap[emailKey]) {
        // First occurrence - store the entire row
        emailMap[emailKey] = {
          rowData: [...finalData[i]],
          companyNames: [finalData[i][companyNameIdx]],
          brands: [],
          hasActiveProperty: isActive == 1,
          hasLapsedProperty: isActive == 0
        };
        
        // Add brand if it exists
        const brand = finalData[i][brandsIdx];
        if (brand) {
          const brandStr = brand.toString().trim();
          // Rename AMI Accommodations to Anna Maria Island Accommodations
          const normalizedBrand = brandStr === 'AMI Accommodations' ? 'Anna Maria Island Accommodations' : brandStr;
          emailMap[emailKey].brands.push(normalizedBrand);
        }
      } else {
        // Duplicate email - add the company name
        const companyName = finalData[i][companyNameIdx];
        if (companyName && !emailMap[emailKey].companyNames.includes(companyName)) {
          emailMap[emailKey].companyNames.push(companyName);
        }
        
        // Add brand if it exists and isn't already in the list
        const brand = finalData[i][brandsIdx];
        if (brand) {
          const brandStr = brand.toString().trim();
          // Rename AMI Accommodations to Anna Maria Island Accommodations
          const normalizedBrand = brandStr === 'AMI Accommodations' ? 'Anna Maria Island Accommodations' : brandStr;
          if (!emailMap[emailKey].brands.includes(normalizedBrand)) {
            emailMap[emailKey].brands.push(normalizedBrand);
          }
        }
        
        // Track if this owner has both active and lapsed properties
        if (isActive == 1) emailMap[emailKey].hasActiveProperty = true;
        if (isActive == 0) emailMap[emailKey].hasLapsedProperty = true;
      }
    }
    
    // Clear all data rows (keep header)
    if (finalData.length > 1) {
      sheet.deleteRows(2, finalData.length - 1);
    }
    
    // Build consolidated data
    const consolidatedData = [];
    Object.keys(emailMap).forEach(emailKey => {
      const entry = emailMap[emailKey];
      const rowData = entry.rowData;
      
      // Combine company names with line breaks
      rowData[companyNameIdx] = entry.companyNames.join('\n');
      
      // Combine brands with semicolons (for HubSpot multi-select)
      // Add leading semicolon for HubSpot appending
      if (brandsIdx !== -1 && entry.brands.length > 0) {
        rowData[brandsIdx] = ';' + entry.brands.join(';');
      }
      
      // Set Homeowner Type based on whether they have active properties
      if (homeownerTypeIdx !== -1) {
        if (entry.hasActiveProperty) {
          rowData[homeownerTypeIdx] = 'Prime Owner';
          rowData[isActiveIdx] = 1; // Set Is Active to 1
        } else if (entry.hasLapsedProperty) {
          rowData[homeownerTypeIdx] = 'Lapsed Owner';
          rowData[isActiveIdx] = 0; // Set Is Active to 0
        }
      }
      
      consolidatedData.push(rowData);
    });
    
    // Write consolidated data back
    if (consolidatedData.length > 0) {
      sheet.getRange(2, 1, consolidatedData.length, consolidatedData[0].length).setValues(consolidatedData);
    }
    
    consolidatedCount = consolidatedData.length;
    Logger.log('Consolidated ' + originalRowCount + ' rows into ' + consolidatedCount + ' unique contacts');
  }
  
  Logger.log('Complete!');
  SpreadsheetApp.getUi().alert('HubSpot preparation complete!\n\nRows consolidated: ' + originalRowCount + ' → ' + consolidatedCount);
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('HubSpot Prep')
    .addItem('Prepare for HubSpot', 'prepareForHubSpot')
    .addToUi();
}

5. Name the project & select Prepare for HubSpot from the drop-down menu.

6. Click Run.

7. Click Allow Permissions and use your Gmail credentials to log in.

8. Return to your spreadsheet which should now be updated.

9. Delete the Is Active column.

10. The spreadsheet is ready for import!