0

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?

2
0

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.

1
  • I realise this is the answer from the duplicate asked by the OP at Doc Editors Help but it also seems like a "code only" answer.
    – Tedinoz
    Oct 25 at 1:36
0

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)

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.