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)
}