CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersyoutube
Matthew Labate
Matthew Labate

Reputation: 1

Google Script Every Minute Trigger

Anybody having problems with the every minute script trigger in Google scripts?

Below is a function to pull YT data for a single video and write to a spreadsheet, which we run for an hour when we publish a video.

The every minute trigger used to work fine, and now it stopped working.

If anybody has any thoughts they would be welcome!

function STATSScript() {


  var ss = SpreadsheetApp.getActiveSpreadsheet();
//  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Generator");
  var sheets = ss.getSheets();

//   if (sheets.length > 1) {
//     Logger.log(sheets[0].getName());
 //  Logger.log(sheets[1].getName());
     //Logger.log(sheets[2].getName());
// }

//  var sheet = ss.getSheetByName("Generator"); //or whatever you name your sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Generator");
  var apiKey = 'KEY'; //leave the single quotes
  var vidId = sheet.getRange("c3").getValue(); 
 // Logger.log(ss);

  var url = 'https://www.googleapis.com/youtube/v3/videos?id=' + vidId + '&key=' + apiKey + '&part=snippet,contentDetails,statistics,status'; // replace this with your API client ID
  //take a look at the raw JSON for this request at https://www.googleapis.com/youtube/v3/videos?id=ID=snippet,contentDetails,statistics,status
  var currentTime = new Date();

  var response = UrlFetchApp.fetch(url); // get feed
  var json = response.getContentText(); // 
  var data = JSON.parse(json);
  //  Logger.log(data);
  var stats = [];


    stats.push(data.items[0].snippet.title); //video title

  //    Logger.log(data.items[0].snippet.title);

    stats.push(data.items[0].statistics.viewCount); //view count
    stats.push(data.items[0].statistics.likeCount); //like count
    stats.push(data.items[0].statistics.dislikeCount); //dislike count

    stats.push(data.items[0].statistics.commentCount); //comment count

    stats.push(data.items[0].snippet.publishedAt); //publish date
    stats.push(data.items[0].snippet.channelTitle); //title of the channel or user
    stats.push("https://www.youtube.com/watch?v="+vidId); //link
    stats.push(currentTime); //time script ran

    SpreadsheetApp.getActiveSheet().appendRow(stats)

  Logger.log(stats);
  Logger.log("Row Data Written");

}



// -----------------------------------------------------------------------------
// function to clear data
// -----------------------------------------------------------------------------
function clearData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Generator');

  sheet.getRange("A2:A3").clearContent();

  // clear out the matches and output sheets
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.getRange(5,1,lastRow-1,9).clearContent();
  }
    Logger.log("Data Cleared");
}



function archiveSheet () {
//Archive the Sheet
   var sheet = SpreadsheetApp.getActiveSpreadsheet();
   var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
   var lastrow = sheet.getLastRow();
   var range = sheet.getRange('A1:N' + (lastrow) + '');
   var date = new Date();
   var formattedDate = Utilities.formatDate(date, "CST", "MM-dd-yy")
   var titleofvid = ss.getRange("a3").getValue(); 
  var titlefilename = formattedDate+' '+titleofvid;
   sheet.setNamedRange('Archive', range);
   var TestRange = sheet.getRangeByName('Archive').getValues(); 
   Logger.log(TestRange); 
   var destFolder = DriveApp.getFolderById('ID'); 
   DriveApp.getFileById(sheet.getId()).makeCopy(titlefilename, destFolder);   
}

function cloneGoogleSheet() {

  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheets()[0];
  var destination = SpreadsheetApp.openById('ID');

 sheet.copyTo(destination);

}

Upvotes: 0

Views: 3329

Answers (1)

ramram
ramram

Reputation: 1

That is simple, you only have a limited amount of time so running it every minute gets you too many runs. Check out your daily quota useage by logging into your Google account.

See here for details: https://developers.google.com/apps-script/guides/services/quotas By using Google script you can make programs that run according to your scheduled.

Google says that you can not call functions more then a fixed number of times within one day. The quotas link tell how much, it also depends whether you use the free edition or a whether you pay for it. So if you script runs a long time or many short times you are bound to get through your max daily run amounts.

By running your script every minute you can also cause the situation in which one script is running and taking more then a minute and a second one starts. The script runtime for consumers is 6 minutes according to the link above. You can only run 30 scripts at the same time so you can definitely get a serious problem..
So watch the timings that google lists and you should be able to avoid problems. Alternatively you can for example make your script so that it handles a queue of things to do and then stops.

Upvotes: -3

Related Questions