0

I am trying to change my scripts to use the new google analytics API within Google Docs.

I am perfectly able to retrieve my data as an array from the api which might look like:

[["01", "5", "5"], ["02", "0", "0"], ["03", "2", "2"], ["04", "2", "6"], ["05", "46", "73"], ["06", "15", "18"], ["07", "7", "7"]]

Where I am looking for some help is on how to write this to a cell. I used to do so with the v2 API, but am struggling here. (just do not understand why my old method is not working).

What I would like to do:

  1. Call a the function from a cell (randomly chosen)

  2. drop the results from the array to a range in my sheet. The cell where I call the function should be the first cell to write the data.

The beginning of the function would be:

function testAnalytics() {

      var id = "ga:XXXXXXXX";
      var startdate = "2012-01-01";
      var enddate = "2012-07-31";
  var metrics = "ga:visits, ga:pageviews";
  var optArgs = {dimensions: "ga:month"};
  var grabData = Analytics.Data.Ga.get(id,startdate, enddate,metrics,optArgs); 
//  Browser.msgBox(grabData.getRows()); // test to see if data is correctly received 
  var returnVal = grabData.getRows();
  return returnVal; 

/* write returnVal to active cell on active spreadsheet */

}
ronalchn
  • 12,225
  • 10
  • 51
  • 61
user1648565
  • 1

1 Answers1

0

Try 

var sheet = SpreadsheetApp.getActiveSheet(); 
var thisCell = SpreadsheetApp.getActiveRange(); 
var row = thisCell.getRow();
var col = thisCell.getColumn(); 
//Assuming every row in the 2D array has the same number of elements. 
sheet.getRange(row, col, returnVal.length , returnVal[0].length).setValues(returnVal);

If you want it to write to any other cell, change thisCell appropriately. Note I haven't tested this myself so if there are syntax errors, please feel free to correct them :)

Srik
  • 7,907
  • 2
  • 20
  • 29
  • Thanks for your help Srik, but it is not working. Actually, this is what I had (more or less) in my old function... driving me nuts! – user1648565 Sep 05 '12 at 11:01
  • Actually, your code is helping me somewhat more. If i run the script now from the script-manager menu, it is actually working. But, When i call my function from a cell by typing "=testAnalytics()", it throws an server error. Any ideas? – user1648565 Sep 05 '12 at 11:07
  • Just completing above remark: when using =testAnaytics() within a cell, I am calling at least one variable (but tried with multiple). I am aware that a function within a cell needs at least one before it can function. So what is driving me nuts is that it is working if all details are hard-coded within the script and the script is called from the manager. But when creating a function to call from a cell where I use one of the variable (earlier hardcoded in script), it is not working... Anyone who can help on this?? – user1648565 Sep 14 '12 at 07:59