Update contacts csv file by adding headers/columns and updating values

update_contacts.js

function modifyForWeichertImport() {

  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var deleteSecondaryAddressColumn = true
  var secondaryAddressColumnNumber = 4

  if (deleteSecondaryAddressColumn) {
    // delete secondary address column
    sheet.deleteColumn(secondaryAddressColumnNumber);
  }
  

  // set header columns
  addHeader(sheet, deleteSecondaryAddressColumn)
  var removeRowsWithoutEmailFlag = true // set this to false if you do not want to remove rows with blank email
  var phoneOnColumn = 9

  if (deleteSecondaryAddressColumn) {
    phoneOnColumn = 8
  }
  var textOnColumn = phoneOnColumn + 2
  var emailOptinColumn = phoneOnColumn + 4
  var status_column = phoneOnColumn + 5
  var deal_type_column = phoneOnColumn + 6
  var hashtagsColumn = phoneOnColumn + 7 // 4th column after email column
  var lastRow = sheet.getLastRow()
  Logger.log("# of records " + lastRow) 

  var startRow = 2
  removeRowsWithoutEmailFlag ? removeRowsWithoutEmail(sheet, startRow, lastRow, phoneOnColumn): ""
  
  // update rowCount, some rows might have been deleted in previous step
  if (removeRowsWithoutEmailFlag) {
    lastRow = sheet.getLastRow()
    Logger.log("# of records after removing rows with no email " + lastRow) 
  }

  populateRowsInColumn(sheet, startRow, status_column, lastRow, "PROSPECT")
  populateRowsInColumn(sheet, startRow, deal_type_column, lastRow, "SELLER")
  var hashTag = sheet.getRange(2, hashtagsColumn).getValue()

  populateRowsInColumn(sheet, startRow, hashtagsColumn, lastRow, hashTag)
  populateRowsInColumn(sheet, startRow, emailOptinColumn, lastRow, "TRUE")
  
  updateOptionColumn(sheet, startRow, phoneOnColumn, lastRow)
  updateOptionColumn(sheet, startRow, textOnColumn, lastRow)

}

function removeRowsWithoutEmail(sheet, startingRow, rowCount, phoneOnColumn){
  var emailColumnNumber = phoneOnColumn + 3
  var rowsToDelete = []
  for (i=startingRow;i <= rowCount;i++) {
    emailColumn = sheet.getRange(i, emailColumnNumber, 1, 1)
    var email = emailColumn.getValue()
    if (email.length == 0) {
      // Logger.log("No email address in row " + i)
      rowsToDelete.push(i)
      // sheet.deleteRow(i);
    }
  }
  for (var i = rowsToDelete.length - 1; i>=0; i--) {   
    sheet.deleteRow(rowsToDelete[i]);   
  }
}

function addHeader(sheet, deleteSecondaryAddressColumn) {
  sheet.insertRowBefore(1);
  // set column range
  var row1 = sheet.getRange("A1:P1")
  if (deleteSecondaryAddressColumn) {
    row1 = sheet.getRange("A1:O1")
    row1.setValues([['first_name', 'last_name', 'primary_address', 
                    'primary_city', 'primary_state','primary_zip', 'primary_phone',
                    'phone_on', 'cell_phone','text_on',	'email',	'email_optin',
                    'status',	'deal_type','hashtags']])
  } else {  
    row1.setValues([['first_name', 'last_name', 'primary_address', 'secondary_address', 
                    'primary_city', 'primary_state','primary_zip', 'primary_phone',
                    'phone_on', 'cell_phone','text_on',	'email',	'email_optin',
                    'status',	'deal_type','hashtags']])
  }
}

function updateOptionColumn(sheet, startingRow, columnNumber, rowCount) {
  var optionColumn
  var i
  for (i=startingRow;i <= rowCount;i++) {
    optionColumn = sheet.getRange(i, columnNumber, 1, 1)
    var columnValue = optionColumn.getValue()
    if (columnValue == "DNC") {
      optionColumn.setValue("FALSE")
    } else if (columnValue == "OK") {
      optionColumn.setValue("TRUE")
    } else if (columnValue.length == 0) {
      optionColumn.setValue("TRUE")
      // Logger.log(i + "--" + columnValue + "--" + columnValue.length)
    }
  } 
}

function populateRowsInColumn(sheet, startingRow, columnNumber, rowCount, value) {
  var statusColumn
  var i
  for (i=startingRow;i <= rowCount;i++) {
    statusColumn = sheet.getRange(i, columnNumber, 1, 1)
    statusColumn.setValues([[value]])   
  } 
}