CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
HSHO
HSHO

Reputation: 525

how to insert Cells data into the last row with Apps Script

I have created this function which copies the cell B2 and paste into the last row of Column A..

But i am getting an error Exception: Invalid argument.

Your help will be appreciated.

enter image description here

function CopyandPaste() {

            var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName('Master List')
  var dataToCopy = sheet.getRange('B3');

  var Direction=SpreadsheetApp.Direction;
  var Lastrow = sheet.getRange("A"+(sheet.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

  var dataToCopy = sheet.getRange('B3');
  var sourceValues = dataToCopy.getValues();

  sheet.getRange(Lastrow + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  dataToCopy.clear({contentsOnly:true});
    }

Upvotes: 0

Views: 946

Answers (1)

Cooper
Cooper

Reputation: 64062

Copy and Paste Mutiple Ranges all at once

This a fun project for a cold afternoon. If allows you to select multiple cells that you wish to copy data from. You make multiple selections as long as each selection is only one row tall or one column wide. To see the selection and move the selected cell out of the keep pressing the ctrl key and make another selection and look as see if it's correct and the you reclick on your last selection to remove.

Most of the time you probably make single selection on a given line but unfortunately the methods provide tend to join touch cell together and I wanted to break apart into separate cell so I could see the from to relationships.

Any the multiple selections can get complicate so I leave that for you to play.

I'll tray to make some notes in the code. To explain some of the more difficult things but if you have questions I'm okay with adding more.

The html is pretty simple two text boxes and three button. The to and the from buttons capture the to and from selection and the execute button takes those ranges and copies data from the from and to the to. I know that sounds stupid to say but that's what it does.

html:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>
<style>input {margin:2px 5px 1px 0;}</style>
<body>
  <input type="text" id="txtfrom" placeholder="Copy from RangeList" size="40" /><input type="button" value="Get From" onclick="getFromRangeList();"/>
  <br /><input type="text" id="txtto" placeholder="Copy to RangeList" size="40" /><input type="button" value="Get To" onClick="getToRangeList()" />
  <br /><input type="button" value="Execute" onClick="doIt();" />
  <script>
    function getFromRangeList() {
        google.script.run
        .withSuccessHandler( (obj) => {document.getElementById("txtfrom").value = obj.a1})
        .getFromRangeList();
      }
      function getToRangeList() {
        google.script.run
        .withSuccessHandler((obj) => {document.getElementById("txtto").value = obj.a1})
        .getToRangeList();
      }
      function doIt() {
        let obj = {};
        obj.to = document.getElementById("txtto").value;
        obj.from = document.getElementById("txtfrom").value;
        google.script.run
        .withSuccessHandler((obj) => {google.script.host.close()} )
        .doIt(obj);
      }
      console.log("My Code")
  </script>

</body>

</html>

gs:

function getFromRangeList() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rgl = sh.getActiveRangeList();
  //this is where the code gets tough because it breaks the sections that get automatically combines on the spreadsheet back into individuaul cells that I need
  let a1 = rgl.getRanges().reduce((a, r) => {
    let h = r.getHeight();
    let w = r.getWidth();
    [...Array.from(new Array(w).keys())].forEach(idx => { a.s.push(r.getCell(1, idx + 1).getA1Notation()); });
    if (h > 1) {
      [...Array.from(new Array(h - 1).keys())].forEach(idx => { a.s.push(r.getCell(idx + 2, 1).getA1Notation()); });
    }
    return a;
  }, { s: [], msg: '', getA1: function () { return this.s.join(',') } }).getA1();
  Logger.log(a1);
  return { a1: a1 };
}

function getToRangeList() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rgl = sh.getActiveRangeList();
  let a1 = rgl.getRanges().reduce((a, r) => {
    let h = r.getHeight();
    let w = r.getWidth();
    [...Array.from(new Array(w).keys())].forEach(idx => { a.s.push(r.getCell(1, idx + 1).getA1Notation()); });
    if (h > 1) {
      [...Array.from(new Array(h - 1).keys())].forEach(idx => { a.s.push(r.getCell(idx + 2, 1).getA1Notation()); });
    }
    return a;
  }, { s: [], msg: '', getA1: function () { return this.s.join(',') } }).getA1();
  Logger.log(a1);
  return { a1: a1 };
}

The following function is probably the simplest of all and it's the one you need to call to the process running. I launches the dialog that assists you in collecting all of the ranges to and from.

function launchCopyPasteDialog() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  ui.showModelessDialog(HtmlService.createHtmlOutputFromFile('ah1').setWidth(600), 'Copy/Paste')
}
function doIt(obj) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  if (obj.from && obj.to) {
    const rglf = sh.getRangeList(obj.from.split(',')).getRanges();
    const rglt = sh.getRangeList(obj.to.split(',')).getRanges();
    rglf.forEach((r, i) => {
      let fs = r.getSheet().getName();
      let ts = rglt[i].getSheet().getName();
      r.copyTo(rglt[i]);
    });
    return { msg: 'All Done' }
  }
}

The section that does the expansion of ranges like A1:C1 back into A1,B1,C1. It's something I've played with for a while. The [...Array.from(new Array(w).keys())]` creates a sequential array of integers 0...to W-1 which i use to calculate the A1Notation() of all of the cells in each ranges it handles all of the ranges for simplicity even the ones that only had one in the first place.

Each new range is added to what use to called the reduce methods accumulater. I still call the accumlator and I probably will until I die.

The accumulator is actually an object that has a function in that allows me to call the method getA1() which returns the contents of a.s which is where all of the ranges A1's are stored during the reduction.

[...Array.from(new Array(w).keys())].forEach(idx => { a.s.push(r.getCell(1, idx + 1).getA1Notation()); });

demo:

enter image description here

Upvotes: 2

Related Questions