CODEWITHSUNDEEP
google-app-enginegoogle-apps-scriptgoogle-appsyoutube-data-api
GPP
GPP

Reputation: 2315

Passing YouTube data into Google sheets column

My goal is to pass in a YouTube channel's id and call that channel's video view metrics for a given window of time, store that data in an array and then pass each video view metric into one column of array.length in descending order. 

  //...key variables
  var channelId = sheet.getActiveCell().getValue();
  var offsetCell = sheet.getActiveCell().offset(0,2); 

 //further down the script...

  for(i=0; i < dataResponse.items.length; i++) {
    var data = [];
    var vidDataLen = dataResponse.items.length;
    var offsetRange = sheet.getRange(offsetCell, vidDataLen, 0)
    data.push(dataResponse.items[i].statistics.viewCount);
    //sheet.appendRow(data)
    offsetCell.setValue([data])
  }

As you can follow, I initially used appendRow, but I didn't want data to be appended into overwriting rows, I wanted the data to pass into, the column and row of offsetCell, and descending into the succeeding cells.

My efforts with

var offsetRange = sheet.getRange(offsetCell, vidDataLen, 0)

were attempting to create an active range based on the number of items in the data array, since that will vary on each channel.

However, that did not work, because getRange does not accept objects as parameters.

Are there any google-script functions I could use to pass the array data into an offset cell and successive cells in that same column?

NOTE: Running this script with the offsetRange variable commented out results in the YouTube api being successfully called, however, only one value from the 'data' array is passed into offsetCell

NOTE 2: I have scoured StackOverflow for google scripted questions and related subjects, however I have not seen an active or relevant solution for passing multiple stored values from an API call into a specific range of offset cells (in one column).

Upvotes: 0

Views: 90

Answers (1)

GPP
GPP

Reputation: 2315

The solution for this was relatively straight forward

I removed the data array and used the statement sheet.insertRows(offsetCell.getRow()+1,vidDataLen);

to create the range of cells in the column I wanted to pass data into so existing data would not be overwritten

  var vidDataLen = dataResponse.items.length;  
   sheet.insertRows(offsetCell.getRow()+1,vidDataLen);

  for(i=0; i < dataResponse.items.length; i++) {
    offsetCell.setValue(dataResponse.items[i].statistics.viewCount);
    offsetCell = offsetCell.offset(1, 0);
  }

Instead of passing data into a loop array, I just passed each value directly into the setValue() method's argument, and then assigned offsetCell to offsetting each new iteration of offsetCell by 1 row and 0 columns.

Upvotes: 1

Related Questions