ImportXML using Google Spreadsheets and Save Results Over-Time

Question

I'm using ImportXML on Google Spreadsheets to scrape YouTube search results and capture the rankings of a video I'm tracking.

=IMPORTXML("https://www.youtube.com/results?search_query=control+theory&page=1"),"//div[@class='yt-lockup-byline']//a")

The scraping works well (and I get all the results for the first page). I then lookup the position of the YouTube results and save that in a cell.

However, the issue I'm having is when I'm trying to track that ranking over-time. Not sure how to do this but I'm looking for a way to run the =ImportXML function every day (is there a refresh command?) and save the result from that cell in a new row each time the function is run.

Any ideas?

Unknown Unknown · Accepted Answer · 2016-05-27T21:01:36

importXML output is automatically updated (about every two hours), but you still need a script to save the past values somewhere: usually, on another sheet. Enter the following using Tools > Script Editor, adjusting the sheet names and the cell with the value of interest. Set it to run daily or weekly using Resources > Current project's triggers.

function storeValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  // where importXML is
  var value = sheet.getRange("B3").getValue(); // where the cell of interest is
  var sheet2 = ss.getSheetByName('Sheet2'); // where to store the data
  var height = sheet2.getLastRow();   
  sheet2.insertRowAfter(height);
  sheet2.getRange(height+1, 1, 1, 2).setValues([[new Date(), value]]);
}

At each run, the script appends two cells at the bottom of Sheet2: one with the timestamp (should be formatted as such), one with the value corresponding to that timestamp.

A version for storing a range of values such as B3:B7:

function storeValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  
  var values = sheet.getRange("B3:B7").getValues()[0];  
  var sheet2 = ss.getSheetByName('Sheet2'); // where to store the data
  var height = sheet2.getLastRow();   
  sheet2.insertRowAfter(height);
  sheet2.getRange(height+1, 1, 1, 6).setValues([[new Date()].concat(values)]); 
  // setting 6 cells instead of 2 on the previous line
}

ImportXML using Google Spreadsheets and Save Results Over-Time

1 Answers