TechQA.

Copy paste values of specific cells in same Google sheet

77 views Asked by At

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

python google-apps-script google-sheets-api sourceforge-appscript
Original Q&A
1

There are 1 answers

0
Cooper On

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:

enter image description here

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:

enter image description here

Related Questions in PYTHON

Related Questions in GOOGLE-APPS-SCRIPT

Related Questions in GOOGLE-SHEETS-API

Related Questions in SOURCEFORGE-APPSCRIPT

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions