How to set background color to google sheet

[Eshwar Chettri]

Hi,

I want to know how to set background color to alternate rows, I have written code but Its setting color to all the rows , Here is my code.

function exportToSpreadsheet(duplicateKeywordGroupsArr, sheet, accountName,campaignIDs) {

  var colTitleColor = "#03cfcc"; // Aqua

  var headers = ["Account Name", "Campaign Id", "Campaign Name", "AdGroup Id","Adgroup Name", "Keyword","Keyword Status","Keyword MatchType","QualityScore","Impressions","Cost","Clicks","Conversions"];

  //getRange(row #, column #, num of Rows, num of Columns)

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setBackground(colTitleColor).setFontWeight("BOLD");

  var dupkeywords=new Array();

   var whereStatements = "AND CampaignId IN [" + campaignIDs.join(",") + "] ";

  

  if (IGNORE_PAUSED_ADGROUPS) {

    whereStatements += "AND AdGroupStatus = ENABLED ";

  } else {

    whereStatements += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";

  }

  

 if (IGNORE_PAUSED_KEYWORDS) {

    whereStatements += "AND Status = ENABLED "; 

  } else {

      whereStatements += "AND Status IN ['ENABLED','PAUSED'] ";

  }

  var dateRange = DATE_RANGE;

   var rowsArray = new Array();

  for(var i=0;i<duplicateKeywordGroupsArr.length;i++){

  

  var kwmtsplit = duplicateKeywordGroupsArr[i].split("::");

   var query = AdWordsApp.report("SELECT AccountDescriptiveName, CampaignId, AdGroupId, CampaignName,QualityScore,Impressions,Ctr, Cost, AdGroupName, Criteria,Clicks,Conversions, KeywordMatchType,Status " +

    "FROM  KEYWORDS_PERFORMANCE_REPORT " +

    "WHERE Criteria ='"+kwmtsplit[0]+ "' and KeywordMatchType='"+kwmtsplit[1].toUpperCase()+"'  " + whereStatements + "DURING " + dateRange);

  var LIGHT_GREY = "#B39E06"; // Grey

  var WHITE = "#D0D3D4"; // White

    var reportIterator = query.rows();

  var keywordArray = new Array();

  while (reportIterator.hasNext()) {

    var kw = reportIterator.next();

    var rowIdx = 2;

     rowsArray.push([kw["AccountDescriptiveName"], kw["CampaignId"], kw["CampaignName"], kw["AdGroupId"], kw["AdGroupName"], kw["Criteria"], kw["Status"], kw["KeywordMatchType"],kw["QualityScore"],kw["Impressions"],kw["Cost"],kw["Clicks"],kw["Conversions"]] );

      }

    var bgColor = (i % 2)?LIGHT_GREY:WHITE;

    sheet.getRange(rowIdx, 1, rowsArray.length, headers.length).setValues(rowsArray).setBackground(bgColor);//Writing final values to sheet

    rowIdx += rowsArray.length;//Incrementing row index

  }

  Logger.log("Number of Duplicate Keywords: " + rowsArray.length);

   

}

[Vincent Racaza (AdWords Scripts Team)]

Hi Eshwar,

It seems that there is something wrong on your third parameter on the sheet.getRange() line as the third parameter indicates how many rows you are getting. Since your third parameter is the rowsArray.length, then, you are getting multiple rows based on the length and apply background color to them. The result would be a one background color for the multiple rows. The value of the third parameter must be 1 to get only one row and apply background color to that row only. I suggest that you add a logger before sheet.getRange():

Logger.log(rowIdx + " " + rowsArray.length);

Also, you can refer to my code snippet below which applies alternate colors per row in the sheet:

var i = 0;
while (rows.hasNext()) {
    var row = rows.next();

    var LIGHT_GREY = "#B39E06"; // Grey
    var WHITE = "#D0D3D4"; // White

    var bgColor = (i % 2)?LIGHT_GREY:WHITE;

    //Sample data from report rows
    sheet.appendRow([row['AccountDescriptiveName'],row['CampaignName'],row['StartDate'],row['Date'],row['DayOfWeek'],
                     row['HourOfDay'],row['VideoViews'],row['Impressions'],row['AverageCpv'],row['Cost']]);
    sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).setBackground(bgColor);
    i++;
 }

Furthermore, so I can check what's wrong in your script based on your data, could you provide to me your CID and script name (Reply privately to author)? Also, grant me access to your spreadsheet.

Thanks,

Vincent Racaza

AdWords Scripts Team