some demo here

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

// test for antonio
// test 2
// test3
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Bids')
    .addItem('Optimize Bids', 'optimizeBid')
    .addToUi();
}

// global configuration
var configSheetName = "config";

/*
-Filter to add as overview critera: Column B = Record Type. Make sure it's both Keywords and Product Targeting
-Create functions for repetitive comparison, getting values as parameters and spitting confirmations;
-Refactor de code so as to users can easily read it and understand it;
-Add comments to each line, explaining/translating it so that non-programmers can understand it;
-Make sure that the file sctructure, data types are correct, data format is correct so that the code runs. If not, highlight it to
 the user for them to correct it;
-After the code has run, check the result to see if it passes tests for integrity;
-Check for existence of needed columns/data prior to running, so that it acommodates slightly different formats, input file wise;
-Set up a repository where this code version's can be consistently managed (Github?);
-Verify if the code structure acccomodates Sponsored Brands Tab
*/

function optimizeBid() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  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

  const configData = configSheet.getRange(1, 1, configSheet.getLastRow(), configSheet.getLastColumn()).getValues();//Gets the values from sheet Config
  //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 Worked');
  let configMatchTypeColPosition = configTabHeaders.indexOf('Match Type');
  let clickThresholdColPosition = configTabHeaders.indexOf('Click Threshold');
  let targetAcosColPosition = configTabHeaders.indexOf('Target Acos');

  //Gets the values from Sponsored Product Campaign 
  let spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat();
  //let spcTabHeaders = spcData[0]; //Gets the first row of SPC tab to check where the columns' positions are
  let spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword or Product Targeting');

  //Adds new Columns, if they don't exist yet
  let newColumns = ['CPC', 'New Bid'];//Columns' names to be checked and added, if they don't exist yet
  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 of 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) 
    }
  }
  spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat(); //Gets the values from Sponsored Product Campaign  
  //spcTabHeaders = spcSheet.getRange(1, 1, spcSheet.getLastRow(), spcSheet.getLastColumn()).getValues().flat(); //Gets the updated first row of SPC tab to check where the columns' positions are

  //Columns' names that come in the Sponsored Product Campaigns tab.
  spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword or Product Targeting');
  let recordTypeColPosition = spcTabHeaders.indexOf('Record Type');
  let spcMatchTypeColPosition = spcTabHeaders.indexOf('Match Type');
  let spcOrdersColPosition = spcTabHeaders.indexOf('Orders');
  let spcAcosColPosition = spcTabHeaders.indexOf('ACoS');
  let spcClicksColPosition = spcTabHeaders.indexOf('Clicks');
  let spcSpendColPosition = spcTabHeaders.indexOf('Spend');
  let spcNewBidColPosition = spcTabHeaders.indexOf('New Bid');
  spcNewBidColPosition = spcNewBidColPosition + 1;//Adds to get the column to receive the calculated value below
  let spcCPCColPosition = spcTabHeaders.indexOf('CPC');
  let maxBidColPostition = spcTabHeaders.indexOf('Max Bid');

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

  //Locates Spend and Clicks columns' ranges (letter) so as to build the formulas for CPC column;
  let spcSpendColRange = spcSheet.getRange(2, spcSpendColPosition + 1, 1, 1).getA1Notation(); //spcSpendColPosition gets + 1, because indexOf starts at zero
  let spcClicksColRange = spcSheet.getRange(2, spcClicksColPosition + 1, 1, 1).getA1Notation(); //spcClicksColPosition gets + 1, because indexOf starts at zero
  let cpcFormula = '=IFERROR(' + spcSpendColRange + '/' + spcClicksColRange + ';0)'; //Builds the formula for row of CPC column
  let spcCPCcolRange = spcSheet.getRange(2, spcCPCColPosition + 1, spcSheet.getLastRow(), 1);//Gets the range in the format for receiving the formula
  spcCPCcolRange.setFormula(cpcFormula).setNumberFormat("€##0.00").setVerticalAlignment("middle");
  let spcData = spcSheet.getRange(1, 1, spcSheet.getLastRow(), spcSheet.getLastColumn()).getValues();
  SpreadsheetApp.flush()
  for (let spcRow = 0; spcRow < spcData.length; spcRow++) { //Goes through the rows of Sponsored Product Campaign tab
    for (let configRow = 0; configRow < configData.length; configRow++) { //Goes through over the rows of config tab

      //Gets search terms values of both SPC and config tabs by accessing the row 
      //iterated over and Search Term Column Position, therefore accessing the cell value.
      let configSearchTerm = configData[configRow][configSearchTermColPosition];
      let spcSearchTerm = spcData[spcRow][spcSearchTermColPosition];

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

      //Gets Record Type from SPC tab and checks if it contains any of the types listed in the variable recordTypesAccepted;
      let spcRecordType = spcData[spcRow][recordTypeColPosition];

      //Checks if SPC tab row value matches any of the record types accepted...
      if (recordTypesAccepted.indexOf(spcRecordType) > -1 && configSearchTerm === spcSearchTerm && configMatchType === spcMatchType) {
        //Defines variables for each of the values that will be compared 
        let spcClicks = spcData[spcRow][spcClicksColPosition];
        let spcOrders = spcData[spcRow][spcOrdersColPosition];
        let spcAcos = spcData[spcRow][spcAcosColPosition];
        let cpc = spcData[spcRow][spcCPCColPosition];
        let maxBid = spcData[spcRow][maxBidColPostition];

        let clickThreshold = configData[configRow][clickThresholdColPosition];
        let targetAcos = configData[configRow][targetAcosColPosition];

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

        let row = spcRow + 1;

        //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);
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos > 0.2) {
          let newBid = cpc * .8;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < 0 && acosMinusTargetAcos > -0.05) {
          let newBid = cpc * (1 - acosMinusTargetAcos);
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < -0.05) {
          let newBid = cpc * 1.075;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcOrders = 0 && spcClicks >= clickThreshold && spcClicks < (clickThreshold + 0.50)) {
          let newBid = cpc * 0.90;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcOrders = 0 && spcClicks >= clickThreshold + 0.50) {
          let newBid = cpc * 0.80;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271");

        } else if (spcOrders = 0 && spcClicks < clickThreshold) {
          let newBid = maxBid;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid);

        } else {
          let newBid = maxBid;
          spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid);
        }
      }
    }
  }
  //Formats New Bid column
  let spcNewBidRange = spcSheet.getRange(2, spcNewBidColPosition, spcSheet.getLastRow(), 1);
  spcNewBidRange.setNumberFormat("€##0.00").setVerticalAlignment("middle");

  //Looks for a record sheet and deletes it, so that a new, updated one is 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)')
  }

  //Rename New Bid Column into Max Bid
  spcSheet.getRange(1, spcNewBidColPosition).setValue('Max Bid');
  spcSheet.deleteColumn(maxBidColPostition + 1);
  spcSheet.deleteColumn(spcCPCColPosition);

  //Brings user to the Record tab
  recordSheet.getRange("A1").activateAsCurrentCell();

  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;
}