JavaScript/AppsScript Google Sheets: Loop Copy and Paste Cell Value Below, While Condition is Met

Background:

I’ve been trying to do what seems like a fairly simple task with Apps Script and Google Sheets; but it seems my Javascript/AppsScript knowledge is lacking.

I have one column that has Event data. Wherein there are many random string values between a recurring roundstart and roundend.

A roundstart will always be followed by a roundend. However, there are events that occur in between rounds, and thus outside of a given roundstart/roundend.

I have looked over MANY similar questions, and all seem to have some fundamental difference in the problem.

Summary:

  • Iterate through every cell in the Round column
  • Start repeating a cell value when the value to the left = roundstart
  • Stop repeating the cell value when the value to the left = roundend

Current Structure:

[Event]     [Round]
  a       
  b
roundstart    1
  x
  y
  z
roundend
  a
  g
  k
roundstart    2
  h
  g
roundend
roundstart    3
  a
  z
roundend


Desired Output:

[Event]     [Round]
  a       
  b
roundstart    1
  x           1
  y           1
  z           1
roundend      1
  a           
  g
  k
roundstart    2
  h           2
  g           2
roundend      2
roundstart    3
  a           3
  z           3
roundend      3
  g
  b

Current Attempts (if you can call them that):

The logic behind this seems straightforward, but I encounter an error in just about every attempt I make. Suspect it is due to my poor understanding of JS syntax…

function Repeat_Rounds() {
  var events_sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rounds_range = event_sheet.getRange("B2:B200").getValues();
//Feel like there is probably a better way to call out the Rounds column range, but this works for now

 rounds_range.forEach(function(cell) {

//if the cell is not null and the cell one column left != "roundend", then...
    if cell.value > 0 and cell.offset(0,-1).value != "roundend" { 

//...copy & paste the current value, one row down
      cell.value.copyTo(cell.offset(0,1))   

     }
  });
}

Source: Ask Javascript Questions

LEAVE A COMMENT