Google sheets. Custom script to move random value from on column to another-CodePudding

I would like a custom script that can move a random value from one column to another

Example:

Before

After

Can this somehow achieved?

CodePudding user response：

Try

function myFunction() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName(`MY_SHEET_NAME`)

  const colAValues = sheet.getRange(`A2:A`)
                          .getDisplayValues()
                          .filter(String)

  const colBValues = sheet.getRange(`B2:B`)
                          .getDisplayValues()
                          .filter(String)
  
  const randomIndex = Math.floor(Math.random() * colAValues.length)
  const randomValue = colAValues.splice(colAValues.indexOf(randomIndex), 1)
  colBValues.push(randomValue)

  sheet.getRange(`A:B`).clearContent()
  sheet.getRange(2, 1, colAValues.length, 1).setValues(colAValues)
  sheet.getRange(2, 2, colBValues.length, 1).setValues(colBValues)

}