Using Google Sheets To Track Your Partner Enablement Business: Creating a Master Partner Tracker Synced To Salesforce

This is Part 2 of a series - you may want to start with Part 1.

In the last chapter we reviewed how to get data IN to Salesforce using Google Sheets, so we could begin the hard part, which is building a dashboard that syncs with Salesforce, from which we can track Partner progress through Certification and Enablement programs, as well as engagement.

Let's start by building the first dashboard. What I want is a list of all partners, with some key data about the partner so I can sort on Channel Manager, Region, Type, etc. Some of our partners also get designated as "focus", since we don't yet have any tiering, so we need to track this too. Then each subsequent column tracks their progress. These columns are either static values, or roll-ups of other sheets (which we'll dive into later). This allows us to "roll-up" all the values in this dashboard, which we can create charts and graphs on later (another chapter, stay tuned!).

For now, we need to sync the partners into a usable format, and add those columns later, so let's sync them first. We have to decide what we want to sync, and create a report that includes the data we need. For this project, I created a report that had the following fields in the report:

And I set the "Account Name" field to sort from A-Z. The finished report looks something like this:

And I like a lot about this report - I don't necessarily need the Account ID (although we'll keep that, as you'll see from the later chapters, we'll need it), but I like the idea of hyperlinking the Account Names on my sheet so I can easily access the SF record when I want. I need to know the Account Owner, the region and the type so I can sort easily. So this report will become my new baseline.

To get this report into your dashboard sheet, create a new tab that will serve as the point of synchronization, the "master database". I created a sheet and called it "All Partners (Syncs to SF)". Then open the Salesforce Extension and select "Reports".

Select the report you just created and import to this new, Active Sheet:

The resulting sheet looks like this:

This is pretty good - now I'm going to play a bit with the format to see if it makes sense with my overall dashboard, the first tab. I laid out a sample of what I would like to see, which looks like this:

Ideally, what I would like is a clickable link that goes right to the partner, with a filter applied so I can sort on the relevant owner, region and type, and the columns on the left will be the values I add and track. When I started this dashboard I didn't know the full breadth yet of what I would track, but I threw some things out there on those columns, and I encourage you to do the same. Since the goal is to sync the first 4 columns with Salesforce, and to keep the data attached to the partner in this sheet, I can change everything after column E relatively easily.

I also only need the Account ID if I want to dynamically construct the hyperlink - Salesforce records are hyperlinked using the following format:

https://<salesforce domain>/lightning/r/Account/<AccountID>/view        

So we'll need to have this value somewhere to create this formula in the sheet after sync. That will be stored in column A and hidden in the dashboard.

Okay so now that we know the final form, let's write some AppScript to get us there. Open "Extensions" and click "Appscript". Delete everything in "Code.gs" and let's start adding our blocks. The first thing we need to do is define our values:

var salesforceURL = "https://<salesforce domain>/lightning/r/Account/";
var sourceSheetName = "All Partners (Syncs to SF)"; 
var targetSheetName = "Overall Scorecard";         

Next, we need a function to create the hyperlinked partner names when they are synced into the target sheet:

function setHyperlinksOnAccountName(sheetName,urlPrefix){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  // Iterate through each row in the sheet (skip the header)
  for (var i = 2; i <= sheet.getLastRow(); i++) {
    var cellA = sheet.getRange(i, 1); // Column A (e.g., Account ID)
    var cellB = sheet.getRange(i, 2); // Column B (where the hyperlink should be)
    
    var accountId = cellA.getValue(); // Get the value from column A
    var accountName = cellB.getValue();
    var formulaB = cellB.getFormula(); // Get the formula in column B, if any
    
    // Check if the cell in column B already contains a HYPERLINK formula
    if (!formulaB.includes("HYPERLINK")) {
      // Construct the hyperlink formula
      var hyperlinkFormula = '=HYPERLINK("' + urlPrefix + accountId + "/view" + '", "' + accountName + '")';
      
      // Set the hyperlink formula in column B
      cellB.setFormula(hyperlinkFormula);
    }
  }
}        

and finally, we need to add a function for that initial sync, and call it. This function is going to get all the data in our synced sheet, and dump it into the Overall Scorecard. Note, this isn't what we want in the long term, but it's a good start. It uses the function above to add the hyperlinks, and hides column A afterwards, which will preserve the associated Account ID but hide it from view:

function initialSync(urlPrefix, sourceSheetName, targetSheetName) {
 // Open the active spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the source and target sheets
  var sourceSheet = spreadsheet.getSheetByName(sourceSheetName);
  var targetSheet = spreadsheet.getSheetByName(targetSheetName);

  // Get the range of data in the source sheet, skipping the header column, sorted
  var sourceRange = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).sort({column: 2, ascending: true});

  // Get the values from the source sheet
  var sourceValues = sourceRange.getValues();

  // Find the last row in the target sheet
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);

  setHyperlinksOnAccountName(targetSheetName, urlPrefix);
  
  // Hide the AccountID column in the target
  targetSheet.hideColumns(1);

  // If there's already a filter, remove all existing filters and re-add one (this is to encapsulate all the new data)
  if (targetSheet.getFilter()) {
    targetSheet.getFilter().remove();
  }

  // Create a new filter on the first row
  targetSheet.getRange(1, 1, targetSheet.getLastRow(), targetSheet.getLastColumn()).createFilter();

}

initialSync(salesforceURL, sourceSheetName, targetSheetName);        

Add all of these blocks, in order, then save Code.gs by using the floppy icon. Test your extension by running it! Keep an eye on that Overall Scorecard, and watch it populate!

My Overall Scorecard went from this:

To this:

And the hyperlinks take me right to Salesforce and the corresponding record!

Now I have to come up with the scoring system, and possibly clean up the Region a bit, but this is completely sortable, and ready for data entry. In the next chapter, we'll both set this report to auto-sync on a schedule, and create a script that compares each row and inserts new partner records into the sheet, while preserving all the data on the right-hand side.

If you liked this article, consider subscribing to the newsletter, because I put an edition out every month!