Copy paste values of specific cells in same Google sheet

Question

Copy paste values of specific cells in same Google sheet

77 Views Asked by Sandeep Parihar At 04 September 2023 at 09:00

I have a Google sheet I want to move only values of specific cells to specific cells from one tab to another at required destination only when I will click a move button present in the tab How will it be done

I know only basics of copy paste but not in detail

Original Q&A

There are 1 best solutions below

**Cooper** · Answer 1 · 2023-09-04T15:19:16.063000

Assuming that you wish to move them to the same cell locations on another sheet:

Select the cells on the source sheet before running the function.

function moveSelectCells() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const dsh = ss.getSheetByName("Sheet1");
  const rgl = sh.getActiveRangeList(); 
  const brgl = breakUpRangeList(ss,sh,rgl);
  brgl.getRanges().forEach((rg,i)=> {
    let rgA1 = rg.getA1Notation();
    dsh.getRange(rg.getA1Notation()).setValue(rg.getValue())
  });
}

Support Function

function breakUpRangeList(ss = SpreadsheetApp.getActive(), sh = ss.getSheetByName("Sheet0"), rgl) {
  let b = [];
  rgl.getRanges().forEach(rg => {
    rg.getValues().forEach((r, i) => {
      let row = rg.getRow() + i;
      r.forEach((c, j) => {
        let col = rg.getColumn() + j;
        b.push(sh.getRange(row, col).getA1Notation())
      })
    })
  })
  b = [...new Set(b)];
  Logger.log(JSON.stringify(b));
  return sh.getRangeList(b);
}

Since your question was incomplete I imagine you will have follow up questions.

A different solution:

google apps script:

function getSourceRangeList() {
  console.log("source range list")
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const srcrgl = breakUpRangeList(ss, sh, sh.getActiveRangeList());
  return { src: srcrgl.getRanges().map(rg => rg.getA1Notation()), srcsht: sh.getName() }
}

function getDestinationRangeList() {
  console.log("destination range list")
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const desrgl = breakUpRangeList(ss, sh, sh.getActiveRangeList());
  return { des: desrgl.getRanges().map(rg => rg.getA1Notation()), dessht: sh.getName() }
}

function launchRangeListMoveDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'), "Move Seleccted Values")
}

function makeYourMove(obj) {
  console.log(JSON.stringify(obj));
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName(obj.srcsht);
  const sshrgl = ssh.getRangeList(obj.srcrgl.split(','));
  const srgs = sshrgl.getRanges().map(rg => rg.getA1Notation());
  const dsh = ss.getSheetByName(obj.dessht);
  const dshrgl = dsh.getRangeList(obj.desrgl.split(','));
  const drgs = dshrgl.getRanges().map(rg => rg.getA1Notation());
  srgs.forEach((rg, i) => {
    dsh.getRange(drgs[i]).setValue(ssh.getRange(srgs[i]).getValue());
    ssh.getRange(srgs[i]).clearContent();
  });
}

html: ('ah2')

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>input{padding:2px 5px 2px 0px;margin:5px;}</style>
  </head>
  <body>
    <form>
      <input type="text" id="srcrgl" name="srcrgl" /> Source RangeList
      <input type="text" id="srcsht" name="srcsht" /> Source Sheet Name
      <br><input type="button" value="Get Src RGL" onClick="getSrcRgl();" />
      <br><input type="text" id="desrgl" name="desrgl" /> Destination RangeList
      <input type="text" id="dessht" name="dessht" /> Destination Sheet Name
      <br><input type="button" value="Get Dest RGL" onClick="getDestRgl();" />
      <br><input type="button" value="Move" onClick="moveContents(this.parentNode);" />
    </form>
    <script>
      function getSrcRgl() {
        google.script.run.withSuccessHandler((obj) => {document.getElementById("srcrgl").value=obj.src.join(',');document.getElementById("srcsht").value=obj.srcsht;})
        .getSourceRangeList();
      }
      function getDestRgl() {
        google.script.run.withSuccessHandler((obj) => {document.getElementById("desrgl").value=obj.des.join(',');document.getElementById("dessht").value=obj.dessht})
        .getDestinationRangeList();
      }
      function moveContents(obj) {
        google.script.run.withSuccessHandler(()=>{google.script.host.close()})
        .makeYourMove(obj);
      }
    </script>
  </body>
</html>

Demo:

The thing I really like about functions with dialogs is that you can really tweak them a lot to do things that you want to add laterl

gas:

function getSourceRangeList() {
  console.log("source range list")
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const srcrgl = breakUpRangeList(ss, sh, sh.getActiveRangeList());
  return { src: srcrgl.getRanges().map(rg => rg.getA1Notation()), srcsht: sh.getName() }
}

function getDestinationRangeList() {
  console.log("destination range list")
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const desrgl = breakUpRangeList(ss, sh, sh.getActiveRangeList());
  return { des: desrgl.getRanges().map(rg => rg.getA1Notation()), dessht: sh.getName() }
}

function launchRangeListMoveDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'), "Move Seleccted Values")
}

function testmym() {
  makeYourMove({ "desrgl": "A1,A2,A3,A4", "srcsht": "Sheet0", "dessht": "Sheet1", "srcrgl": "B4,C5,D6,E7" });
}

function makeYourMove(obj) {
  console.log(JSON.stringify(obj));
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName(obj.srcsht);
  const sshrgl = ssh.getRangeList(obj.srcrgl.split(','));
  const srgs = sshrgl.getRanges().map(rg => rg.getA1Notation());
  const dsh = ss.getSheetByName(obj.dessht);
  const dshrgl = dsh.getRangeList(obj.desrgl.split(','));
  const drgs = dshrgl.getRanges().map(rg => rg.getA1Notation());
  srgs.forEach((rg, i) => {
    dsh.getRange(drgs[i]).setValue(ssh.getRange(srgs[i]).getValue());
    if(!obj.toggleCopy) {
      ssh.getRange(srgs[i]).clearContent();
    }
  });
}

html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      input{padding:2px 5px 2px 0px;margin:5px;}
    </style>
  </head>
  <body>
    <form>
      <input type="text" id="srcrgl" name="srcrgl" /> Source RangeList
      <input type="text" id="srcsht" name="srcsht" /> Source Sheet Name
      <br><input type="button" value="Get Src RGL" onClick="getSrcRgl();" />
      <br><input type="text" id="desrgl" name="desrgl" /> Destination RangeList
      <input type="text" id="dessht" name="dessht" /> Destination Sheet Name
      <br><input type="button" value="Get Dest RGL" onClick="getDestRgl();" />
      <br><input type="button" id="movebtn" value="Move" onClick="moveContents(this.parentNode);" /><input id="mdm" type="checkbox" name="toggleCopy" onClick="toggleCheck();"/> Don't delete source values
    </form>
    <div id="msg"></div>
    <script>
      function getSrcRgl() {
        google.script.run.withSuccessHandler((obj) => {document.getElementById("srcrgl").value=obj.src.join(',');document.getElementById("srcsht").value=obj.srcsht;})
        .getSourceRangeList();
      }
      function getDestRgl() {
        google.script.run.withSuccessHandler((obj) => {document.getElementById("desrgl").value=obj.des.join(',');document.getElementById("dessht").value=obj.dessht})
        .getDestinationRangeList();
      }
      function moveContents(obj) {
        let sl = document.getElementById("srcrgl").value.split(",").length;
        let dl = document.getElementById("desrgl").value.split(",").length;
        if(sl != dl) {
          window.alert("Number of Source and Destination elements does not match");
          document.getElementById("srcrgl").focus();
          return;
        }
        google.script.run.withSuccessHandler(()=>{google.script.host.close()})
        .makeYourMove(obj);
      }
      function toggleCheck() {
        let st = document.getElementById('mdm').checked;
        if(st) {
          document.getElementById("movebtn").value = "Copy";
        } else {
          document.getElementById("movebtn").value = "Move"
        }
      }
    </script>
  </body>
</html>

Demo:

Copy paste values of specific cells in same Google sheet

There are 1 best solutions below

Related Questions in PYTHON

Related Questions in GOOGLE-APPS-SCRIPT

Related Questions in GOOGLE-SHEETS-API

Related Questions in SOURCEFORGE-APPSCRIPT

Trending Questions

Popular # Hahtags

Popular Questions