latest version

appsscript.json

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "webapp": {
    "access": "ANYONE",
    "executeAs": "USER_ACCESSING"
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "sheets": {
    "macros": [{
      "menuName": "SP prepare sheet",
      "functionName": "SPpreparesheet",
      "defaultShortcut": "Ctrl+Alt+Shift+1"
    }]
  }
}

RankWinner.gs

//GLOBALS:

//Choose which columns to hide in the Record Sheet for easier visualization of the resulting sheet
let colsToHide = [1, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 25, 26, 27, 28, 39, 40, 41, 42, 43, 44, 45];
let newBidBackgroundColor = "#D8E271"; //Choose the background color of the adjusted bids. For getting the hex of other colors, visit: https://htmlcolorcodes.com/
let newBidFormatting = "€##0.00" //Sets currency formatting to Bids

//Sets Custom Menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Bids')
    .addItem('Optimize Bids for [Sponsored Products]', 'optimizeBid')
    .addToUi();
}

/*
*It gets parameters set in Config Tab and compares with the data coming in the Sponsored Products Campaigns tab
*so that the it can run the calculations and adjust the Max Bid column with the suggested values.
*It creates a copy of the tab for analysis purposes.
*/
function optimizeBid() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();//Gets the current file
  const configSheet = ss.getSheetByName('config'); //Maps the sheet where threshold and other paramters are
  const spcSheet = ss.getSheetByName('Sponsored Products Campaigns'); //Maps the sheet where the values coming from Amazon are

  //Gets the values from sheet Config
  const configData = configSheet.getRange(1, 1, configSheet.getLastRow(), configSheet.getLastColumn()).getValues();

  //Gets the positions of the columns names in the config tab.
  const configTabHeaders = configData[0]; //Gets the first row of config tab to check where the columns' positions are
  let configSearchTermColPosition = configTabHeaders.indexOf('Search Term');
  let configMatchTypeColPosition = configTabHeaders.indexOf('Match Type');
  let clickThresholdColPosition = configTabHeaders.indexOf('Click Threshold');
  let targetAcosColPosition = configTabHeaders.indexOf('Target Acos');
  let rankingColPosition = configTabHeaders.indexOf('Ranking');
  let targetRankingColPosition = configTabHeaders.indexOf('Target Ranking');
  let campaignIdColPosition = configTabHeaders.indexOf('Campaign ID');

   //Sets the default values, in case the Search Terms don't match with the ones listed in Config tab
  var defaultClickThreshold = configData.filter(e => e[0] == 'default').map(e => e[clickThresholdColPosition]);
  var defaultTargetAcos = configData.filter(e => e[0] == 'default').map(e => e[targetAcosColPosition]);
  var defaultTargetRanking = configData.filter(e => e[0] == 'default').map(e => e[targetRankingColPosition]);

  //Identifies the position of the Keywork or Product Targeting column
  let spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat();//Converts 2D into a 1D array for getting the Search term col position
  let spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword Text');

  //Adds new Columns, if they don't exist yet
  let newColumns = ['Calculation Record', 'New Bid'];//Columns' names to be checked and added
  for (let newColumn = 0; newColumn < newColumns.length; newColumn++) {//Iterates over the newColumns elements
    if (spcTabHeaders.indexOf(newColumns[newColumn]) === -1) { //Checks if each of them exists in the spreadsheet header row
      let newcolPosition = spcSearchTermColPosition + 1;//Gets 01 position on the left to the column Search Terms
      spcSheet.insertColumnsBefore(newcolPosition, 1);//Inserts the column
      spcSheet.getRange(1, newcolPosition).setValue(newColumns[newColumn]);//Names the column added
      newcolPosition++;//Increments the iteration to go through the next word in the array (newColumns), if any
    }
  }

  //Gets the UPDATED header values from Sponsored Product Campaigns tabd
  spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat();

  /*
  *Columns that come in the Sponsored Product Campaigns tab and whose names are to match, so the script can get their positions.
  *Calculations will run based on these columns and, therefore, their names cannot change.
  */
  spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword Text');//Gets its updated position after new column(s) has been added
  let entityColPosition = spcTabHeaders.indexOf('Entity');
  let spcMatchTypeColPosition = spcTabHeaders.indexOf('Match Type');
  let spcOrdersColPosition = spcTabHeaders.indexOf('Orders');
  let spcAcosColPosition = spcTabHeaders.indexOf('Acos');
  let spcClicksColPosition = spcTabHeaders.indexOf('Clicks');
  let spcNewBidColPosition = spcTabHeaders.indexOf('New Bid');
  spcNewBidColPosition = spcNewBidColPosition + 1;//Adds to get the column to receive the calculated value below
  let spcCalcRecordColPosition = spcTabHeaders.indexOf('Calculation Record');
  let spcCPCColPosition = spcTabHeaders.indexOf('CPC');
  let spcBidColPositition = spcTabHeaders.indexOf('Bid');
  let spcCampaignIdColPosition = spcTabHeaders.indexOf('Campaign Id');
  let spcOperationColPosition = spcTabHeaders.indexOf('Operation');
  let productTargetingExColPosition = spcTabHeaders.indexOf('Product Targeting Expression');

  //Populated Operation Column with: Update  ...as per new Amazon's requirement
  //spcSheet.getRange(2, spcOperationColPosition + 1, spcSheet.getLastRow() - 1, 1).setValue('Update');

  //Record Types to be checked on SPC tab and then and run calculations for rows related to them
  let recordTypesAccepted = ['Keyword', 'Product Targeting'];//If need be, more types can be added like so (e.g.: ['Keyword', 'Product Targeting',' Another Type'])

  //Makes sure column formats are set to numbers
  formatColumn(spcSheet, spcBidColPositition + 1, 'decimalNumber');// Format which will be applied: 0.00
  formatColumn(spcSheet, spcAcosColPosition + 1, 'percentage'); // Format which will be applied: 0.00%
  formatColumn(spcSheet, spcCPCColPosition + 1, 'decimalNumber'); // Format which will be applied: 0.00
  formatColumn(spcSheet, spcClicksColPosition + 1, 'number');// Format which will be applied: 00
  formatColumn(spcSheet, spcOrdersColPosition + 1, 'number');// Format which will be applied: 00

  //Gets SPC updated data
  let spcData = spcSheet.getRange(1, 1, spcSheet.getLastRow(), spcSheet.getLastColumn()).getValues();

  //Gets New Bid Columns' values and formatting so that they can get updated and set back at once, after getting adjusted
  let newBidRng = spcSheet.getRange(1, spcNewBidColPosition, spcSheet.getLastRow(), 1);
  let newBidValues = newBidRng.getValues();
  let newBidBackgrounds = newBidRng.getBackgrounds();
  let newBidFormats = newBidRng.getNumberFormats();

  //Gets the Calculation Record column's values
  let calcRecordsRng = spcSheet.getRange(1, spcCalcRecordColPosition + 1, spcSheet.getLastRow(), 1);
  let calcRecords = calcRecordsRng.getValues();

  let operationRng = spcSheet.getRange(1, spcOperationColPosition + 1, spcSheet.getLastRow(), 1);
  let operationsValues = operationRng.getValues();

  //Goes through each row of both Config and SPC tabs
  for (let spcRow = 0; spcRow < spcData.length; spcRow++) {
    for (let configRow = 0; configRow < configData.length; configRow++) {

      //Gets search terms values from both SPC and config tabs by accessing the row being iterated over 
      //and Search Term columns' positions to get the values for comparisons
      let configSearchTerm = configData[configRow][configSearchTermColPosition];
      let spcSearchTerm = spcData[spcRow][spcSearchTermColPosition];

      //Checks if the Entity contains Product Targeting and get Product Targeting Expression as the Search Term
      let entity = spcData[spcRow][entityColPosition];
      if (entity == 'Product Targeting') {
        spcSearchTerm = spcData[spcRow][productTargetingExColPosition]
      }

      //Gets Match Type terms values of both SPC and config tabs
      let configMatchType = configData[configRow][configMatchTypeColPosition];
      let spcMatchType = spcData[spcRow][spcMatchTypeColPosition];

      let configCampaignId = configData[configRow][campaignIdColPosition];
      let spcCampaignId = spcData[spcRow][spcCampaignIdColPosition];

      var spcRanking = configData.filter(e => e[configSearchTermColPosition] == spcSearchTerm);//Based on Search term, finds the ranking in configData
      spcRanking = spcRanking.map(x => x[rankingColPosition]);//Sets the ranking to spc Search Term based on what is in the config file

      //Gets rows' values to be tested prior to calculations
      var spcClicks = spcData[spcRow][spcClicksColPosition];
      var spcOrders = spcData[spcRow][spcOrdersColPosition];
      var spcAcos = spcData[spcRow][spcAcosColPosition];
      var cpc = spcData[spcRow][spcCPCColPosition];
      var bid = spcData[spcRow][spcBidColPositition];

      let clickThreshold = configData[configRow][clickThresholdColPosition] == '' ? defaultClickThreshold : configData[configRow][clickThresholdColPosition];
      let targetAcos = configData[configRow][targetAcosColPosition] == '' ? defaultTargetAcos : configData[configRow][targetAcosColPosition];
      let targetRanking = configData[configRow][targetRankingColPosition] == '' ? defaultTargetRanking : configData[configRow][targetRankingColPosition];

      if (recordTypesAccepted.indexOf(entity) > -1 && configSearchTerm == spcSearchTerm && configMatchType == spcMatchType && configCampaignId == spcCampaignId) {

        //Calls a function spcAcosMinusTargetAcos that calculates rows' data passed in each loop
        var acosMinusTargetAcos = spcAcosMinusTargetAcos(spcAcos, targetAcos);

        //Calculates New Bids based on sets of criteria and places that new bid into the newly created column (New Bid);
        if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos > 0 && acosMinusTargetAcos <= 0.2) {
          let newBid = cpc * (1 - acosMinusTargetAcos);
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 1st if: Clicks > Threshold, Orders >=1, Acos - Target > 0 and < 0.2 then CPC * (1 - ' + acosMinusTargetAcos + ')';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos > 0.2) {
          let newBid = cpc * 0.8;
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 2nd if: Clicks > Threshold, Order >=1, Acos - Target > 0.2 then CPC * 0.80';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < 0 && acosMinusTargetAcos > -0.05) {
          let newBid = cpc * (1 - acosMinusTargetAcos);
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 3rd if: Click > Threshold, Orders >= 1, Acos-Target < 0 and > -0.05 then CPC * (1 - ' + acosMinusTargetAcos + ')';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < -0.05) {
          let newBid = cpc * 1.075;
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 4th if: Clicks > Threshold, Orders >=1, Acos - Target < -0.05 then CPC * 1.075';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcClicks >= clickThreshold && spcOrders == 0 && spcClicks < (clickThreshold + (clickThreshold * 0.50))) {
          let newBid = cpc * 0.90;
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 5th if: Clicks > Threshold, Orders = 0, Clicks < (Threshold + 0.50) then CPC * 0.90';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcOrders == 0 && spcClicks >= (clickThreshold + (clickThreshold * 0.50))) {
          let newBid = cpc * 0.80;
          newBidValues[spcRow][0] = newBid;
          newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 6th if: Orders = 0 and Clicks >= (Threshold + 0.50) then CPC * 0.80';
          operationsValues[spcRow][0] = 'Update';

        } else if (spcOrders == 0 && spcClicks < clickThreshold) {
          newBidValues[spcRow][0] = bid;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 7th if: Orders = 0 and Clicks < Threshold then Bid is kept';

        } else {
          let newBid = bid;
          newBidValues[spcRow][0] = newBid;
          newBidFormats[spcRow][0] = newBidFormatting;
          calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 8th criteria, where Bid is kept';
        }


      } else if (recordTypesAccepted.indexOf(entity) > -1 && spcMatchType === configMatchType && spcCampaignId === configCampaignId && spcClicks > clickThreshold) {
        let newBid = cpc * 0.95;
        newBidValues[spcRow][0] = newBid;
        newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
        newBidFormats[spcRow][0] = newBidFormatting;
        calcRecords[spcRow][0] = 'Keyword, Match Type, Search Term, Campaing ID match. 9th if: Ranking <= TargetRanking & Click > Threshold then CPC * 0.95';
        operationsValues[spcRow][0] = 'Update';
        // if [Rank] <= [Target Rank] and [Match Type matches] and [Campaign ID matches]
        // let newBid = newBid * 0.95;

      } else if (recordTypesAccepted.indexOf(entity) > -1) {
        let newBid = bid;
        newBidValues[spcRow][0] = newBid;
        newBidBackgrounds[spcRow][0] = newBidBackgroundColor;
        newBidFormats[spcRow][0] = newBidFormatting;
        calcRecords[spcRow][0] = 'It\'s Keyword or Product Targeting, but met no other criteria. Bid kept.';
        operationsValues[spcRow][0] = 'Update?';
      } 
    }
  }
  //Sets updated values and formatting to Adjusted Bids
  newBidRng.setValues(newBidValues);
  newBidRng.setBackgrounds(newBidBackgrounds);
  newBidRng.setNumberFormats(newBidFormats);

  //Sets calculation records to newly created for records purpose
  calcRecordsRng.setValues(calcRecords);

  //Sets Operation to Update, if that row meets criteria
  operationRng.setValues(operationsValues);

  //Name Adjusted Bid column for record purpose
  //spcSheet.getRange(1, spcNewBidColPosition).setValue('New Bid');

  //Gets a copy of the processed Sponsored Products Campaign so as to keep it as a record
  //Looks for a record sheet and deletes it, so that an updated one can be created
  var delSheet = ss.getSheetByName("Sponsored Products Campaigns (Record)");
  if (delSheet) {
    ss.deleteSheet(delSheet);
  }
  var recordSheet = spcSheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  recordSheet.setName('Sponsored Products Campaigns (Record)').setFrozenRows(1);

  //Auto-resize all columns in the Record Sheet for better visualization
  recordSheet.autoResizeColumns(1, recordSheet.getMaxColumns());
  for (let col = 0; col < colsToHide.length; col++) {
    recordSheet.hideColumns(colsToHide[col]);
  }
  //Brings user to the Record tab
  recordSheet.getRange("A1").activateAsCurrentCell();

  //Rename New final Bid Column to Bid
  spcSheet.getRange(1, spcNewBidColPosition).setValue('Bid');

  //Deletes columns that won't go in to the file to be uploaded
  spcSheet.deleteColumn(spcBidColPositition + 1);
  spcSheet.deleteColumn(spcCalcRecordColPosition);
  ss.toast('The bids have been recalculated!');
}
//Gets Sponsored Product Campaign Acos minus Target Acos and outputs the result for comparisons run in the function optimizeBid
function spcAcosMinusTargetAcos(spcAcos, targetAcos) {
  return spcAcos - targetAcos;
}

function formatColumn(sheet, columnIndex, format) {
  if (format === 'number') {
    format = "##0"
  } else if (format === 'percentage') {
    format = "##0.00%"
  }
  else if (format === 'decimalNumber') {
    format = "##0.00"
  }
  sheet.getRange(2,columnIndex, sheet.getLastRow(), 1).setNumberFormat(format);
}