How to get a specific data from Google Sheets

[Krzysztof Bycina]

Hello AdWords Scripts Team!

I have a big favor for you :-)

Would you mind telling me how to import a specific data from Google Sheets to AdWords Scripts?

I export a lot of data to Google Sheets using AdWords scripts for reporting.

Now, I would like to do a step forward and import this data & analyse.

This is how a part of my data looks like (it's not a real data):

I'm looking for a way to import parts of it for a different analysis.

My biggest problem is here:

How to import data for a specific date range & itemid.

For example how to import CPCs for "Itemid12933" for date range 2018-02-15 to 2018-02-18

and store it into an object like this: 

newdata = {

itemid: "Itemid12933",

CPC: ["2","3","4","4"]            

Dates: ["2018-02-15","2018-02-16","2018-02-17","2018-02-18"]

It will pull up the CPCs and dates inside the object as arreys and store them one by one.

Is it possible to do it?

I'm sorry if the question is too specific!

Thank you.

Krzysztof

[Adrian Catambay (AdWords Scripts Team)]

Hello Krzysztof,

Could you please confirm the purpose of the imported data from the spreadsheet? Do you intend to create an AdWords Entity out of the imported data, or only a report for analysis purposes? Generally, importing data from a spreadsheet with conditions such as only for a certain value or date range, is not supported in AdWords Scripts. Only entities creation from spreadsheet data is possible. Also, you may refer to this sample code on how to get data from a spreadsheet using the getValues() function, for additional reference.

Thanks,

Adrian

AdWords Scripts Team

[Krzysztof Bycina]

Hi Adrian,

Thank you very much for the answer.

I have another script that tracks data such as prodcuts prices from Google Merchant Center and stores them into Google Sheet.
This script checks the conversion volume on the top converting keywords & Item Ids and let me know if somhing started underperfrming.
Also, the scripts looks at different values and trys to figure it out why the performance dropped.
On of the value is price.
As there is no way to see the historicall prices in AdWords I would like to use data from the Google Sheets.

Maybe there is a workaround to lookup for specfic data range in sheets an pullup the price data?

Please let me know.

Once againg thank you very much!

Kind regards,
Krzysztof

[Krzysztof Bycina]

One more thing:
I'm not sure if undesrastand this script corretly but it seems like Ad Customiser script does something similar:
https://developers.google.com/adwords/scripts/docs/solutions/customizer

// Read flower / quantity / price data from a spreadsheet. In a real
// advertising campaign, you would have your own data source to read from.
function readFlowers(url) {
  var flowersByName = {};
  var spreadsheet = SpreadsheetApp.openByUrl(url);
  var sheet = spreadsheet.getSheets()[0];
  var data = sheet.getRange(2, 1, sheet.getMaxRows() - 1, 3).getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][0]) {
      var flower = {
        name: data[i][0],
        quantity: parseFloat(data[i][1]),
        price: data[i][2]
      };
      if (typeof flower.price != 'string') {
        // Spreadsheets will sometimes coerce "$4.99" into just the number
        // 4.99. In that case, add the dollar sign back.
        flower.price = '$' + flower.price.toFixed(2);
      }
      flowersByName[flower.name] = flower;
    }
  }
  return flowersByName;
}

Maybe I could read whole the column A (Date) and Column B (Prices) and look for the specific values using scripts?

Thank you!

[Adrian Catambay (AdWords Scripts Team)]

Hello Krzysztof,

To get data from a specific range in your spreadsheet, you may use the getRange() function to define the range containing the data you want to pull, and use the getValues() function to get the data from the range you specified. You may refer the sample code snippet below on how to use these functions:

// The code below gets the values for the range C2:G8
// in the active spreadsheet.
var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();
Logger.log(values[0][0]);

As for your other question, yes, you may get data from a spreadsheet using the getRange() and getValues() functions and just search for specific values using scripts.

[Krzysztof Bycina]

Thank you Adrian.

Kind regards,
Krzysztof