I'm trying to integrate the Hubspot Deals to Google spreadsheets, I'm following a tutorial https://medium.com/how-to-lean-startup/create-a-hubspot-custom-dashboard-with-google-spreadsheet-and-data-studio-27f9c08ade8d. But I'm getting an error "Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 2.". Can anyone please help me with the same.Thank you.

var CLIENT_ID = '';     // Enter your Client ID
var CLIENT_SECRET = ''; // Enter your Client secret
var SCOPE = 'contacts';
var AUTH_URL = 'https://app.hubspot.com/oauth/authorize';
var TOKEN_URL = 'https://api.hubapi.com/oauth/v1/token';
var API_URL = 'https://api.hubapi.com';

function getService() {
   return OAuth2.createService('hubspot')
      .setTokenUrl(TOKEN_URL)
      .setAuthorizationBaseUrl(AUTH_URL)
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      .setCallbackFunction('authCallback')
      .setPropertyStore(PropertiesService.getUserProperties())
      .setScope(SCOPE);
}
function authCallback(request) {
   var service = getService();
   var authorized = service.handleCallback(request);
   if (authorized) {
      return HtmlService.createHtmlOutput('Success!');
   } else {
      return HtmlService.createHtmlOutput('Denied.');
   }
}

function authenticate() {
   var service = getService();
   if (service.hasAccess()) {
      // … whatever needs to be done here …
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
   }
}

function getStages() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer '   service.getAccessToken()}};
  
  // API request
  var pipeline_id = "default"; // Enter your pipeline id here.
  var url = API_URL   "/crm-pipelines/v1/pipelines/deals";
  var response = UrlFetchApp.fetch(url, headers);
  var result = JSON.parse(response.getContentText());
  var stages = Array();
  
  // Looping through the different pipelines you might have in Hubspot
  result.results.forEach(function(item) {
    if (item.pipelineId == pipeline_id) {
      var result_stages = item.stages;
      // Let's sort the stages by displayOrder
      result_stages.sort(function(a,b) {
        return a.displayOrder-b.displayOrder;
      });
  
      // Let's put all the used stages (id & label) in an array
      result_stages.forEach(function(stage) {
        stages.push([stage.stageId,stage.label]);  
      });
    }
  });
  
  return stages;
}


function getDeals() {
   // Prepare authentication to Hubspot
   var service = getService();
   var headers = {headers: {'Authorization': 'Bearer '  service.getAccessToken()}};
   // Prepare pagination
   // Hubspot lets you take max 250 deals per request.
   // We need to make multiple request until we get all the deals.
   var keep_going = true;
   var offset = 0;
   var deals = Array();
   while(keep_going) {
      // We’ll take three properties from the deals: the source, the stage & the amount of the deal
      var url = API_URL   "/deals/v1/deal/paged?properties=dealstage&properties=source&properties=amount&limit=250&offset=" offset;
      var response = UrlFetchApp.fetch(url, headers);
      var result = JSON.parse(response.getContentText());
      // Are there any more results, should we stop the pagination
      keep_going = result.hasMore;
      offset = result.offset;
      // For each deal, we take the stageId, source & amount
      result.deals.forEach(function(deal) {
         var stageId = (deal.properties.hasOwnProperty("dealstage")) ? deal.properties.dealstage.value : "unknown";
         var source = (deal.properties.hasOwnProperty("source")) ? deal.properties.source.value : "unknown";
         var amount = (deal.properties.hasOwnProperty("amount")) ? deal.properties.amount.value : 0;
         deals.push([stageId,source,amount]);
      });
   }
   return deals;
}

var sheetNameStages = "Stages";
var sheetNameDeals = "Deals";
function writeStages(stages) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(sheetNameStages);
   // Let’s put some headers and add the stages to our table
   var matrix = Array(["StageID","Label"]);
   matrix = matrix.concat(stages);
   // Writing the table to the spreadsheet
   var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
   range.setValues(matrix);
}
function writeDeals(deals) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(sheetNameDeals);
   // Let’s put some headers and add the deals to our table
   var matrix = Array(["StageID","Source", "Amount"]);
   matrix = matrix.concat(deals);
   // Writing the table to the spreadsheet
   var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
   range.setValues(matrix);
}

function refresh() {
   var service = getService();
   if (service.hasAccess()) {
      var stages = getStages();
      writeStages(stages);
      var deals = getDeals();
      writeDeals(deals);
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
   }
}

CodePudding user response：

The error you encountered is actually an issue on setValues when you are trying to write and there is a mismatch of columns between the data and the range.

Seeing that it says "The data has 0 but the range has 2" means that it is expecting 2 columns, but has received 0 from data.

You have 2 functions that uses setValues to your sheet, and the one that expects 2 columns is most likely the culprit which is writeStages. Try to print the stages and matrix right before using setValues to check the validity of the data before writing and from there, you should be able to identify the cause and fix the issue.