PMax change tROAS from Google spread sheet not working

[Vladyslav Yarovoi]

Hello, 

I am trying to change the tROAS from the google sheet. in the column A2:A there are names of the companies, column B2:B has tRoas. 

I have written the code and it changes the first company from the first row, but it can change the next one.

May you please help me with updating the code? 

Code below:

function main() {
  // URL do Google Sheet
  var sheetUrl = 'https://docs.google.com/spreadsheets/d/1Czq4pDbs-hX8Y6fAyQBNd-Zd8laaqy6GszCE812tEEU/edit?gid=0#gid=0';
 
  // Pobierz nazwę arkusza z linku - zazwyczaj 'Sheet1' lub zmień na odpowiednią
  var sheetName = 'Sheet1';

  // Pobierz nazwy kampanii i docelowy ROAS z Google Sheets
  var campaignData = getCampaignDataFromSheet(sheetUrl, sheetName);

  // Iteruj przez każdą nazwę kampanii i ustaw odpowiedni Target ROAS
  for (var i = 0; i < campaignData.length; i++) {
    var campaignName = campaignData[i].name;
    var targetRoas = campaignData[i].roas;
    Logger.log('Processing campaign: ' + campaignName + ' with Target ROAS: ' + targetRoas);
    setTargetRoasForCampaign(campaignName, targetRoas);
  }
}

// Funkcja pobierająca nazwy kampanii i docelowy ROAS z arkusza Google Sheets
function getCampaignDataFromSheet(sheetUrl, sheetName) {
  // Otwórz arkusz Google Sheets
  var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = spreadsheet.getSheetByName(sheetName);

  // Pobierz zakres danych z kolumn A (nazwa kampanii) i B (docelowy ROAS)
  var range = sheet.getRange('A2:B' + sheet.getLastRow());
  var values = range.getValues();
 
  // Wyciągnij nazwy kampanii i docelowy ROAS z wartości
  var campaignData = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i][0]) { // Sprawdź, czy komórka z nazwą kampanii nie jest pusta
      var campaignName = values[i][0];
      var targetRoas = parseFloat(values[i][1]) || 0; // Pobierz ROAS, jeśli istnieje, w przeciwnym razie ustaw 0
      campaignData.push({name: campaignName, roas: targetRoas});
    }
  }
  return campaignData;
}

// Funkcja ustawiająca Target ROAS dla kampanii
function setTargetRoasForCampaign(campaignName, targetRoas) {
  var campaignIterator = AdsApp.performanceMaxCampaigns()
    .withCondition("Name = '" + campaignName + "'")
    .get();

  // Sprawdź, czy znaleziono kampanię i ustaw ROAS
  if (campaignIterator.totalNumEntities() == 0) {
    Logger.log('No campaign found with the name: ' + campaignName);
  } else {
    while (campaignIterator.hasNext()) {
      var campaign = campaignIterator.next();
      campaign.bidding().setTargetRoas(targetRoas);
      Logger.log('Set Target ROAS to ' + targetRoas + ' for campaign: ' + campaignName);
    }
  }
}

----------------------------------------------------------------------

----------------------------------------------------------------------

Logs below:

6/28/2024 1:40:22 PM
Processing campaign: [AT][PG][CSS1][MAX][Baterie] with Target ROAS: 200
6/28/2024 1:40:23 PM
Set Target ROAS to 200 for campaign: [AT][PG][CSS1][MAX][Baterie]
6/28/2024 1:40:23 PM
Processing campaign: [AT][PG][CSS1][MAX][Sredniomarzowe] with Target ROAS: 250
6/28/2024 1:40:24 PM
Ca: QueryError.BAD_VALUE: Error in WHERE clause: invalid value ". at setTargetRoasForCampaign (Code:57:24) at main (Code:17:5)

[Google Ads Scripts Forum]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

In order to investigate further on your issue, kindly provide us with the below details.

• Google Ads account ID/CID
• Name of the affected script
• Shareable spreadsheet link if you are using in your script

You can share the requested details via Reply privately to the author option or a direct private reply to this email.

Thanks,
Google Ads scripts team