How to format a list of leads for an auto-dialer

If you’re in sales, especially if you’re a sales development representative (SDR) or business development representative (BDR), we know your pain. You need to make cold calls, so you go to ZoomInfo or RocketReach or Apollo.io to get lists of leads.

These sources give you massive spreadsheets of leads, often thousands of rows long, and they’re normally formatted so you can easily feed them into your mass email machines, like Instantly.ai or ReachInbox.io. That’s great, but they’re rarely formatted in a way you can feed easily into your auto-dialer, like JustCall or PhoneBurner.

Usually, multiple phone numbers for each contact are crammed into one column, and each phone number is separated by a comma. Well guess what. The auto-dialers don’t understand that they’re more than one phone number. They think it’s one number. The auto-dialer sees that the phone number isn’t in the correct format and rejects the number.

If you’re like our team at Brain Jar, you probably have been separating them by hand. You want the lead’s name to appear on your screen when it dials the number, so you need to create a separate row for each phone number, and you need the phone numbers to appear in one column.

So one-by-one, you add a new row below the one with the multiple numbers, then you copy-paste the info down, then you put one phone number in each row. And you go on and on and on.

This is a drag when your lists have hundreds of rows, but when your list has thousands or, like ours, tens of thousands of rows, it’s completely impossible. It would take dozens of hours of labor, driving the cost of acquisition sky high.

Well, luckily, Google Sheets has come to the rescue! Because you can run something called Google Apps Script on a Google Sheet. This is basically JavaScript, but with a bunch of built-in objects and methods specific to Google Sheets.

If you’re a sales person (or an SDR or BDR) that last sentence was probably gibberish. After all, you’re not a programmer, so how could you possibly use code to solve your problem? Well have no fear! I’m going to write this blog so that lay people such as yourself will be able to implement it and format your spreadsheets so you can feed it into your auto-dialer beautifully.

So, let’s get started! First, go into your Google Drive and open your Google Sheet. At the top menu, select Extensions > Apps Script

This is going to open a new tab. Go to that tab, and you’ll see an interface you’ve probably never seen before. This is Google Apps Script. It’s a way that you can use code to accomplish very complicated tasks on Google Sheets.

So get ready, salesperson! You’re about to become a programmer. See that compicated code below this paragraph? That’s a programming language called JavaScript. Copy that whole long chunk of code and paste it into the big code area in the center of your screen. 

function processAndDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  // Iterate from the end to avoid issues with row index shifting
  for (var i = values.length - 1; i >= 0; i--) {
    var cellValue = values[i][19]; // Column T is index 19 (0-based index)
    
    if (typeof cellValue === 'string') {
      // Remove left square bracket if it exists
      cellValue = cellValue.replace(/^\[/, '');
      
      // Check if the string contains a comma
      if (cellValue.includes(',')) {
        // Split at comma and get the second part
        var parts = cellValue.split(',');
        var secondPhoneNumber = parts.length > 1 ? parts[1].trim() : '';
        
        // Remove the second part from the original cell value
        cellValue = parts[0].trim();
        
        // Update the original row's Column T with the modified value
        sheet.getRange(i + 1, 20).setValue(cellValue); // Column T is index 20 (1-based index)
        
        // Copy the current row data
        var rowContents = values[i];
        
        // Insert a new row below the current row
        sheet.insertRowAfter(i + 1);
        
        // Paste the copied data into the new row
        sheet.getRange(i + 2, 1, 1, rowContents.length).setValues([rowContents]);
        
        // Update Column T in the new row with the secondPhoneNumber
        sheet.getRange(i + 2, 20).setValue(secondPhoneNumber); // Column T is index 20 (1-based index)
      }
    }
  }
}

Now, we’re going to have to make a change to this code so that it works with your specific Google Sheet. But I’ll walk you through that change step-by-step.

First, what column are your phone numbers in? Mine were in column T when I wrote this code, so we’ll use that for example. Count up the alphabet until you hit that letter. My Google Sheet had the phone numbers in column T, so when I counted up the alphabet, T was letter number 20. Remember that number.

Now, take that number and subtract 1 from it. In my case, the number is 19. Replace the number 19 in my code with whatever number you came up with.

You’re done changing the code! Now, click the “Deploy” button in the upper right corner of your screen, and choose “New Deployment” from the dropdown menu.

In the modal window that appears, select the gear box toward the upper left. Then, in the dropdown menu that appears, select “Web App.”

In the modal window that appears, give your script a name (I named mine “Lead list formatter,” but it doesn’t matter what you name yours) and hit the Deploy button in the lower right corner.

Next, click Authorize Access. A window will appear asking you to choose the Google account that your spreadsheet is stored in. Select that, then an alarming window will appear saying, “Google hasn’t verified this app. The app is requesting access to sensitive info in your Google Account.”

Ignore that noise. Click “Advanced,” then click “Go to untitled project (unsafe).”

The next window that pops up says, “Untitled project wants access to your Google account.” Click “Allow.”

Next you’ll see a window saying, “New deployment. Deployment successfully updated.” Click, “Done.”

Now you’ll be back to the main Goolge Apps Script dashboard. Hit “Run” and wait for the code to process. This will likely take a few minutes, depending on how many rows are in your spreadsheet.

After your Google Apps Script gives you a message that says, “Execution Complete,” go to your Google Sheet and see if it worked. If all went well, there will be no more phone numbers crammed into one cell. Now, all the phone numbers will be in a single column, and for every lead that had more than one phone number, that person now appears in more than one row, each identical except for the phone numbers.

If that’s what it looks like, you’re ready to feed the Google Sheet into your auto-dialer and start making cold calls. Enjoy, and go get ’em!