[TF-SP-Everything-US-Search] Bid 120% On Working Hour, 30% On Off Work.js

function main() 
{
  // Bid Modifier on Off Work Hours
  var LOW_BID = 0.3;
  
  // Bid Modifier on Working Hours
  var HIGH_BID = 1.2;

  // retrieve campaign: TF-SP-Everything-US-Search
  var campaignIterator = AdWordsApp.campaigns().withCondition('Name = "TF-SP-Everything-US-Search"').get();
  
  if (campaignIterator.hasNext()) 
  {
    var campaign = campaignIterator.next();
    
    // data of locations range reading from Google Spreadsheet "TF Sales by Country"
    var data = getLocationsFromSpreadsheet();
    
    for (var i = 0; i < data.length; i++) 
    {
      var locationId = data[i][0];
      var locationName = data[i][1];
      var timeZoneOffset = data[i][4];
      
      var now = new Date();
      var localHour = now.getUTCHours() + timeZoneOffset;
      
      var bid = localHour >= 9 && localHour <= 18 ? HIGH_BID : LOW_BID;
      
      // Find existing targeted locaiton adjustment 
      var locationIterator = campaign.targeting().targetedLocations().withIds([[campaign.getId(), locationId]]).get();
      if (locationIterator.hasNext())
      {
        var targetedLocation = locationIterator.next();
        
        // Update location's bid adjustment
        targetedLocation.setBidModifier(bid);
      }
      else
      {
        // Add location's bid adjustment to the campaign
        campaign.addLocation(locationId, bid);
      }
        
      Logger.log("Add to targeted location '" + locationName + "' (+" + locationId + ") bid = " + bid + "\n");
    }
  }

}


function getLocationsFromSpreadsheet() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1mbqy-ppYSQtiV-uf8BVhkzqPhBoed985ZBjOQnvELKw/');
  var range = ss.getRangeByName("BestsellingUSStates");
  return range.getValues();
}