Copy random row from one Google Sheet to another

Question

I have 2 separate google sheets. (Lets call them Primary and Secondary)

The Primary spreadsheet has 2 columns with 50 rows of data.

On a set interval (weekly) I want to copy only one random row from the Primary spreadsheet and add it as a new row in the Secondary spreadsheet.

Is this possible?

Answer 1

Use a helper sheet that shows a random row from the Primary sheet using filter() and randbetween(), together with a time-driven script such as appendValuesToArchiveSheet.

Answer 2

On a set interval (weekly) I want to copy only one random row from the Primary spreadsheet and add it as a new row in the Secondary spreadsheet. Yes this is possible. There are likely to be many possible solutions, consider this as one approach.

The answer requires a script. There are many precedents that provide one or more elements of the solution, but none that wrap them into a cohesive whole.

The script appendrandomRow must be installed as an Installable Time-Driven, running once a week.

function appendrandomRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // define the sheets
  var primary = ss.getSheetByName('Primary')
  var secondary = ss.getSheetByName('Secondary')

  // get the data from Primary
  var rangetoLog = primary.getDataRange()
  var valuestoLog = rangetoLog.getValues()

  // get last row by using 'length'
  // Logger.log("DEBUG: number of entries = "+valuestoLog.length)
  
  // or get last row by counting values
  // Note started at A2 to avoid header
  var lastRow = primary.getRange("A2:A").getValues().filter(String).length;
  // Logger.log("DEBUG: number of data rows = "+lastRow)
 
  // subroutine to get Random Integer
  // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math/random
  // Getting a random integer between two values
  // Min is inclusive so set to 1
  // Max is exclusive so add "2" 
  var min = Math.ceil(1);
  var max = Math.floor(lastRow+2);
  var randomRow = Math.floor(Math.random() * (max - min) + min); //The maximum is exclusive and the minimum is inclusive
  Logger.log("DEBUG: the random row = "+randomRow+ ", values: "+valuestoLog[randomRow-1][0]+", "+valuestoLog[randomRow-1][1])
 
  // create a temporary array to hold the values from the random row
  var temparray=[]
  // push the values into the atemporary array
  temparray.push(valuestoLog[randomRow-1][0])
  temparray.push(valuestoLog[randomRow-1][1])

  // append the data to the 'Secondary' sheet
  secondary.appendRow(temparray)

}

---