Populate data from the first empty row

[Felicia Tan]

Hi Team,

Had an issue which the script returned no results after adding below part in red. I was trying to get it to populate data from the first empty row. Please help? Thank you.

function main() {

   function jumpToFirstEmpty(){

  var col = "CampaignName"

  

  var SPREADSHEET_URL = 'docs.google.com/spreadsheets/d/10eykuAf7_k1_shcAxGdOf-UNuP6zudpn6cMu7OduN4Q/edit#gid=0';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = ss.getSheetByName('CONS-Raw');

  Logger.log(ss.getName());

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var now = new Date();

  var from = new Date(now.getTime() - 90 * MILLIS_PER_DAY);

  var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var report = AdWordsApp.report(

    'SELECT CampaignName, Clicks, Impressions, Cost,Conversions, SearchRankLostImpressionShare, SearchBudgetLostImpressionShare ' +

    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

    'WHERE  Impressions > 0 and CampaignName CONTAINS "q4"' +  

    'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','

    + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

//  var rows = report.rows();

//  while (rows.hasNext()) {

//    var row = rows.next();

//    var campaignName = row['CampaignName'];

//    var clicks = row['Clicks'];

//    var impressions = row['Impressions'];

//    var cost = row['Cost'];

//    Logger.log(campaignName + ',' + clicks + ',' + impressions + ',' + cost);

//  }

  report.exportToSheet(sheet);

  // Logger.log("Report available at " + spreadsheet.getUrl());

  //Get googlesheet

  

}}

[Google Ads Scripts Forum Advisor]

Hi,

Thanks for reaching out. Harry here, from the Google Ads Scripts Team.

Adding the two lines of code you have highlighted would not let you have the script to only populate data in the first empty. You could instead remove the report.exportToSheet method and refer to the example in this link on how you can update your script to only append data starting from the last row by using the sheet.appendRow method.

Let me know if you need more help.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2R6X11:ref

[Alexz Wong (OMG)]

So what is my next step?

 

Thanks.

 

Alexz Wong 
Assistant Performance Manager

Unit 808, Core E, Cyberport 3

100 Cyberport Road

Hong Kong

Ph: +852 2911 1583 
Fax: +852 2511 6802

 

COMvergences top media Agencies in Hong Kong 2020

Campaign’s Hong Kong Media Agency of the Year 2020 - Gold  

Campaign’s APAC Performance Agency of the Year 2020 - Gold

Marketing Magazine’s Hong Kong Media Agency of the Year 2020 - Gold

Marketing Magazine’s Hong Kong Performance Agency of the Year 2020 - Gold

Marketing Magazine’s Hong Kong Search Agency of the Year 2020 - Gold

WARC’S Most awarded holding company for creativity 2020

 

From: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>
Sent: Friday, November 5, 2021 5:12 PM
To: adwords...@googlegroups.com
Subject: RE: Populate data from the first empty row

 

CAUTION: This email originated from the Internet

 

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/n66s7000000000000000000000000000000000000000000000R23DJS003ArR0kQXR12nuxZDQC5LYQ%40sfdc.net.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

[Felicia Tan]

Hi Harry,

Thank you for the suggestion. I have added the code in red but not sure what else I should add after. Could you please help? Thank you very much. 

function main() {

  

  var SPREADSHEET_URL = 'docs.google.com/spreadsheets/d/10eykuAf7_k1_shcAxGdOf-UNuP6zudpn6cMu7OduN4Q/edit#gid=0';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = ss.getSheetByName('CONS-Raw');

  Logger.log(ss.getName());

  

  sheet.appendRow([

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var now = new Date();

  var from = new Date(now.getTime() - 90 * MILLIS_PER_DAY);

  var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var report = AdWordsApp.report(

    'SELECT CampaignName, Clicks, Impressions, Cost,Conversions, SearchRankLostImpressionShare, SearchBudgetLostImpressionShare ' +

    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

    'WHERE  Impressions > 0 and CampaignName CONTAINS "q4"' +  

    'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','

    + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

//  var rows = report.rows();

//  while (rows.hasNext()) {

//    var row = rows.next();

//    var campaignName = row['CampaignName'];

//    var clicks = row['Clicks'];

//    var impressions = row['Impressions'];

//    var cost = row['Cost'];

//    Logger.log(campaignName + ',' + clicks + ',' + impressions + ',' + cost);

//  }

  // Logger.log("Report available at " + spreadsheet.getUrl());

  //Get googlesheet

  

}

[Google Ads Scripts Forum Advisor]

Hi Alexz,

Let us continue our conversation from instead forum thread instead for better tracking purposes on our end.

@Felicia: Kindly check out the examples in the links I have listed below:

• sheet.appendRow method
• Append rows to a spreadsheet

You should execute the appendRow method when you iterate through the returned report data which would be like the following code:

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME); 

  var report = AdsApp.report(
    'SELECT CampaignName, Clicks, Impressions, Cost ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE  Impressions < 10 ' +
    'DURING LAST_30_DAYS');

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var campaignName = row['CampaignName'];
    var clicks = row['Clicks'];
    var impressions = row['Impressions'];
    var cost = row['Cost'];
    Logger.log(campaignName + ',' + clicks + ',' + impressions + ',' + cost);
    sheet.appendRow([campaignName, clicks, impressions, cost]);
  }

Let me know if you need anything else.

[Felicia Tan]

Hi Harry,

Thank you for getting back. I have added the below line after going through the materials you have sent. After running the script, my whole sheet is refreshed instead of new rows being appended. Could you please help with the troubleshooting?

function main() {

  

  var ss = SpreadsheetApp.openByUrl('docs.google.com/spreadsheets/d/10eykuAf7_k1_shcAxGdOf-UNuP6zudpn6cMu7OduN4Q/edit?usp=sharing');

  var sheet = ss.getSheetByName('BIZ-Raw');

  Logger.log(ss.getName());

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var now = new Date();

  var from = new Date(now.getTime() - 90 * MILLIS_PER_DAY);

  var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var report = AdsApp.report(

    'SELECT CampaignName, Clicks, Impressions, Cost, Conversions, SearchRankLostImpressionShare, SearchBudgetLostImpressionShare ' + 

    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

    'WHERE  Impressions > 0 and CampaignName CONTAINS "q4" ' +  

    'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','

    + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();

    var campaignName = row['CampaignName'];

    var clicks = row['Clicks'];

    var impressions = row['Impressions'];

    var cost = row['Cost'];

    Logger.log(campaignName + ',' + clicks + ',' + impressions + ',' + cost);

    sheet.appendRow([campaignName, clicks, impressions, cost]);

}

  report.exportToSheet(sheet);

  // Logger.log("Report available at " + spreadsheet.getUrl());

  //Get googlesheet

  

}

[Google Ads Scripts Forum Advisor]

Hi Felicia,

Thanks for getting back. Kindly remove the report.exportToSheet method at the lower part of your code. I'd also recommend having a date field for your reference in the report's date range.

Let me of anything else.

[Felicia Tan]

Hi Harry,

Thank you so much for all your help. The script is working well now. Have a great week ahead! :)