JavaScript - Paste the values from a defined range to the first empty cell in another column

Question

I am trying to write a macro that would copy the data from range H23:I32 to adjacent columns J23:K60. Assuming there are values in J23:K60, I wish to paste the data below the last cell with data.

The sheet I am working with is 'Loot'.

Unfortunately, my code doesn't search for the last cell with value and always pastes the data to J23. Thefore my list gets overwritten instead of values being added to it.

function UpdateGroup() {
var spreadsheet = SpreadsheetApp.getActive();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Loot');
const lastRow = sheet.getLastRow();
sheet.getRange("H23:I23" + lastRow).copyTo(sheet.getRange("J23:K23" + (lastRow + 1)), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

};

How do I go about fixing my code so that I have a growing list in J23:K from H23:I values?

Many thanks in advance!

Answer 1

In your situation, how about the following modification?

function UpdateGroup() {
  // This is from https://stackoverflow.com/a/44563639/7108653
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Loot');
  const lastRowH = sheet.get1stNonEmptyRowFromBottom(8);
  const lastRowJ = sheet.get1stNonEmptyRowFromBottom(10);
  sheet.getRange("H23:I" + lastRowH).copyTo(sheet.getRange("J" + (lastRowJ+ 1)), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

• In this modification, the last rows of "H" and "J" are retrieved and use them.

• For example, the last rows of columns "H" and "I" are different and/or the last rows of columns "J" and "K" are different, how about the following modification?

    function UpdateGroup() {
      // This is from https://stackoverflow.com/a/44563639/7108653
      Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
        const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
        return search ? search.getRow() : offsetRow;
      };
  
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Loot');
      const lastRowH = sheet.get1stNonEmptyRowFromBottom(8);
      const lastRowI = sheet.get1stNonEmptyRowFromBottom(9);
      const lastRowJ = sheet.get1stNonEmptyRowFromBottom(10);
      const lastRowK = sheet.get1stNonEmptyRowFromBottom(11);
      sheet.getRange("H23:I" + (lastRowH > lastRowI ? lastRowH : lastRowI))
      .copyTo(sheet.getRange("J" + ((lastRowJ > lastRowK ? lastRowJ : lastRowK) + 1)), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }
  

Added:

From your following reply,

This solved it, thank you!! I did not manage to make the script start checking for last empty cell from J23 and below, but this is not an issue, since I filled J1:J22 with spaces to make these cells 'not blanks'.

In this case, how about the following sample script? In this sample script, when the last rows of the column "J" and "K" are 0, the value is copied to the row 23.

Sample script:

function UpdateGroup() {
  // This is from https://stackoverflow.com/a/44563639/7108653
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Loot');
  const lastRowH = sheet.get1stNonEmptyRowFromBottom(8);
  const lastRowI = sheet.get1stNonEmptyRowFromBottom(9);
  const lastRowJ = sheet.get1stNonEmptyRowFromBottom(10);
  const lastRowK = sheet.get1stNonEmptyRowFromBottom(11);
  const dest = lastRowJ > lastRowK ? lastRowJ : lastRowK;
  sheet.getRange("H23:I" + (lastRowH > lastRowI ? lastRowH : lastRowI))
  .copyTo(sheet.getRange("J" + ((dest >= 22 ? dest : 22) + 1)), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Answer 2

A Bit more of a mechanical approach

GS:

function srcToDst() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile("ah2").setWidth(350).setHeight(150),"SRC-DST");
}

function getSRCRg() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  return {SRC: `'${sh.getName()}'!${rg.getA1Notation()}`};
}

function getDSTRg() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  return {DST: `'${sh.getName()}'!${rg.getA1Notation()}`};
}

function copySRCDSTs(obj) {
  const ss = SpreadsheetApp.getActive();
  ss.getRange(obj.SRC).copyTo(ss.getRange(obj.DST));
}

function moveSRCDSTs(obj) {
  const ss = SpreadsheetApp.getActive();
  ss.getRange(obj.SRC).moveTo(ss.getRange(obj.DST));
}

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
  <script>
    function getSRC() {
      document.body.style.backgroundColor = "#ffff00";
      google.script.run
      .withSuccessHandler(obj => {
        document.getElementById("src").value = obj.SRC;
        document.body.style.backgroundColor = "#ffffff";
      })
      .getSRCRg();
    }
    function getDST() {
      document.body.style.backgroundColor = "#ffff00";
      google.script.run
      .withSuccessHandler(obj => {
        document.getElementById("dst").value = obj.DST;
        document.body.style.backgroundColor = "#ffffff";
      })
      .getDSTRg();
    }
    function moveSRCDST() {
      document.body.style.backgroundColor = "#ffff00";
      google.script.run
      .withSuccessHandler(obj => {
        document.body.style.backgroundColor = "#ffffff";
      })
      .moveSRCDSTs({SRC: $('#src').val(),DST: $('#dst').val()})
    }
    function copySRCDST() {
      document.body.style.backgroundColor = "#ffff00";
      google.script.run
      .withSuccessHandler(obj => {
        document.body.style.backgroundColor = "#ffffff";
      })
      .copySRCDSTs({SRC: $('#src').val(),DST: $('#dst').val()})
    }
    console.log("Move SRC to DST")
  </script>
  <style>
    input{margin:2px 5px 2px 0;}
    #container{width:100%;}
  </style>
  </head>
  <body>
   <form>
      <input type="text" size="25" id="src" placeholder="Select Source Range Press SRC" />
      <input type="button" value="SRC" onClick="getSRC();" />
      <br /><input type="text" size="25" id="dst" placeholder="Select Dest Range Press DST" />
      <input type="button" value="DST" onClick="getDST();" />
      <br /><input type="Button" value="Move" onClick="moveSRCDST();" /> 
      <input type="Button" value="Copy" onClick="copySRCDST();" />
    </form>  
  </body>
</html>

Demo: