Copy values to next column(if blank) in the same row. If not blank, copy to the second column(if blank) in same row. Repeat

Disclaimer: Im very new to google scripts. I jumbled together this code with mixed success.

When I run the script, it works fine with the first two attempts. Then it doesnt work after that because column Q now has values in other cells within the column and the script is technically correct but not running at intended. I need to ignore column Q cells that are not blank and still run the script to copy P values to the other cells in column Q.

Also, when column Q with in the same row is not blank, I need to copy the value from column P to column R (if blank). Rinse and Repeat script…

  function copyVals () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("Sheet1!P2:P");
  var destSheet = ss.getSheetByName("Sheet1");
  var destRange = destSheet.getRange('Q2:Q')
  var destRange2 = destSheet.getRange('R2:R')
    if (destRange.isBlank()) {
  source.copyTo (destRange, {contentsOnly: true});
  source.clear ();
  } 
    if (destRange2.isBlank()) {   
   source.copyTo (destRange2, {contentsOnly: true});
   source.clear ();
  }
}

Answer

You need to do the blank check for each cell separately

However, if you do it wiht the Apps Script method isBlank() – this will make your code a bit slow.

I suggest you to

  • retreive the existing values both in the source and destination ranges with getValues
  • check for each of the destinations values either those are empty and replace the empty values through source values
  • assign the modified values back to the sheet with setValues

Sample code:

function copyVals () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ()
  var sheet = ss.getSheetByName("Sheet1")
  var lastRow = sheet.getLastRow()
  var source = sheet.getRange ("P2:P" + lastRow)
  var destSheet = sheet
  var destRange = destSheet.getRange('Q2:Q' + lastRow)
  var destRange2 = destSheet.getRange('R2:R' + lastRow)
  var sourceValues = source.getValues().flat()
  var destValues = destRange.getValues()
  var dest2Values = destRange2.getValues()
  sourceValues.forEach(function(value, i){
  console.log("i" + i)
    if (destValues[i][0] == "") {
      destValues[i][0] = value
    } 
    if (dest2Values[i][0] == "") {
      dest2Values[i][0] = value
    } 
  })
  destRange.setValues(destValues)
  destRange2.setValues(dest2Values)
  source.clear ();
}

UPDATE

If you want to copy to column Q and R alternately, you can use script properties to save the run count of the script and execute different code blocks for odd and even number.

Sample:

function copyVals () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ()
  var sheet = ss.getSheetByName("Sheet1")
  var lastRow = sheet.getLastRow()
  var source = sheet.getRange ("P2:P" + lastRow)
  var destSheet = sheet
  var destRange = destSheet.getRange('Q2:Q' + lastRow)
  var destRange2 = destSheet.getRange('R2:R' + lastRow)
  var sourceValues = source.getValues().flat()
  var destValues = destRange.getValues()
  var dest2Values = destRange2.getValues()
  
  var scriptProperties = PropertiesService.getScriptProperties()
  var myProperty = scriptProperties.getProperty('timesCalled')
  if (!myProperty){
    myProperty = "1"
    }

    myProperty = JSON.parse(myProperty)
    var isOdd =  myProperty % 2
    if(isOdd){
    sourceValues.forEach(function(value, i){
      console.log("i" + i)
      if (destValues[i][0] == "") {
        destValues[i][0] = value
      }  
    })
    destRange.setValues(destValues)
    
  } else{
    sourceValues.forEach(function(value, i){
      if (dest2Values[i][0] == "") {
        dest2Values[i][0] = value
      } 
    })
    destRange2.setValues(dest2Values)
  }
  source.clear ()
  myProperty++
  scriptProperties.setProperty('timesCalled', JSON.stringify(myProperty))
}