CODEWITHSUNDEEP
functiongoogle-apps-scriptgoogle-sheetsgoogle-apps
Nic Lewis
Nic Lewis

Reputation: 11

How to copy the last (n)rows of a google sheet to a new sheet

I am new to Google Apps and Google sheets and would appreciate a little help. I have a google sheet with some data in a table that is simply a date column and then a few columns of data collected from that date with a new row for each days data. I wish to copy the last 7 days, 30 days, 60 days etc to a new sheets. Essentially copy the last (x)Rows of a table to a new sheet where I can vary (x).

Is there a simply function that I can use on a new sheet to get the desired (x) rows from the table. Or do I have to use a script. If so how do I proceed.

Any replies would be greatly appreciated.

Thank you

Upvotes: 1

Views: 2800

Answers (3)

Alan Wells
Alan Wells

Reputation: 31300

To add a menu, and run code I've created the following code and explanation:

Add a Function the runs when the spreadsheet opens:

Choose TOOLS, SCRIPT EDITOR and the code editor will open. Paste in this onOpen() function.

function onOpen() {

  SpreadsheetApp.getUi()
      .createMenu('Custom Menu')
      .addItem('Display User Dialog', 'displayUserDialog')
      .addToUi();
};

That code will run when you open the spreadsheet. You will see a new Menu Item. That code is set up to trigger some more code when you choose 'Display User Dialog' from the Custom Menu. You need to add more code. Add this function:

function displayUserDialog() {

  //Logger.log('displayUserDialog ran: ');

  var html = HtmlService.createTemplateFromFile('Dialog Copy Choices')
    .evaluate()
    .setSandboxMode(HtmlService.SandboxMode.NATIVE)
    .setWidth(500)
    .setHeight(300);

  SpreadsheetApp.getUi()
    .showModalDialog(html, 'My custom dialog');
};

The above two functions go into a gs script file. I think a new script file is usually automatically created name: Code.gs

Create a new HTML file: From the FILE menu, choose NEW - HTML

HTML File named "Dialog Copy Choices"

<div id="outer" style="padding:1;"/>

<div>
    <br>Sheet To Copy To:<br>
    <select id="idSheetNames">
      <?!= getSheetNames(); ?>
    </select>
    <br>
    <br>
    <div>Copy To:</div>
    <input id="idToCol" type='text' placeholder='Column To Copy to:'>
    <br>
    <input id="idToRow" type='text' placeholder='Row To Copy to:'>
    <br>
    <br>
    <div>Copy From:</div>
    <input id="idFromStart" type='text' placeholder='A1'>
    <br>
    <input id="idFromEnd" type='text' placeholder='D20:'>
    <br>
    <br> 
    <input type="button" value="Copy Cells" id="idCopyBtn" onclick="myFunction()">
</div>

<script>

function myFunction() {
  var sheetToGetData = document.getElementById("idSheetNames").value;
  var copyToRow = document.getElementById("idToRow").value;
  var copyToCol = document.getElementById("idToCol").value;

  var copyFromStart = document.getElementById("idFromStart").value;
  var copyFromEnd = document.getElementById("idFromEnd").value;

  //console.log("values of variables: " + sheetToGetData + " : " + copyToCol + " : " + copyToRow + " : " +  copyFromStart + " : " +  copyFromEnd);

  google.script.run.gsCopyData(sheetToGetData, copyToCol, copyToRow, copyFromStart, copyFromEnd);
  google.script.host.close();
};
</script>

Refresh the spreadsheet after adding all that code, and a new custom menu should appear. Use the custom menu to display the dialog.

The custom dialog displays all the sheets in your spreadsheet in a drop down field. The drop down allows you to choose what sheet to copy the data to:

gs Code to Get Sheet Names

function getSheetNames() {
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var howManySheets = allSheets.length;
  var sheetNames = [];
  var htmlSelectOptions = "";
  var thisName = "";

  for (var i = 0;i < howManySheets; i++) {
    thisName = allSheets[i].getName();
    htmlSelectOptions += '<option value="' + thisName + '">' + thisName + '</option>';
  };

  return htmlSelectOptions;
};

gs Code to Copy Data

function gsCopyData(sheetToGetData, copyToCol, copyToRow, copyFromStart, copyFromEnd) {
  //Logger.log(sheetToGetData + " : " + copyToCol + " : " + copyToRow + " : " + copyFromStart + " : " + copyFromEnd);
  //Logger.log("gsCopyData ran");

  var alphaBet = 'abcdefghijklmnopqrstuvwxyz';

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var rowStartCopy = Number(copyFromStart.slice(1));
  var colStartCopy = copyFromStart.slice(0,1);
  colStartCopy = 1 + alphaBet.indexOf(colStartCopy.toLowerCase());

  var rowEndCopy = Number(copyFromEnd.slice(1));
  var colEndCopy = copyFromEnd.slice(0,1);
  colEndCopy = 1 + alphaBet.indexOf(colEndCopy.toLowerCase());

  var numRows = rowEndCopy - rowStartCopy + 1;
  var numColumns = colEndCopy - colStartCopy + 1;

  var rangeToCopy = sheet.getRange(rowStartCopy, colStartCopy, numRows, numColumns);

  copyToCol = alphaBet.indexOf(copyToCol.toLowerCase()) + 1;

  var destinationSheet = ss.getSheetByName(sheetToGetData);

  //Logger.log(destinationSheet + copyToCol + numColumns + copyToRow + numRows);

  rangeToCopy.copyValuesToRange(destinationSheet, copyToCol, numColumns, copyToRow, numRows);
};

Upvotes: 1

Bjorn Behrendt
Bjorn Behrendt

Reputation: 1264

I use the same technique in an activities schedule I built for the school I work for. In my version I created I created a sheet for today and the next 7 days. https://docs.google.com/spreadsheet/ccc?key=0AnQ7SpwUoM8odDRKZWE2eVh4QTNzOWsyQmlkb3JvRVE&usp=sharing#gid=10

Below is the filter function I used for the next 7 days.

=filter('All Events'!A:H,('All Events'!A1:A1654=DATEVALUE(now()))+('All Events'!A1:A1654=DATEVALUE(now()+1))+('All Events'!A1:A1654=DATEVALUE(now()+2))+('All Events'!A1:A1654=DATEVALUE(now()+3))+('All Events'!A1:A1654=DATEVALUE(now()+4))+('All Events'!A1:A1654=DATEVALUE(now()+5))+('All Events'!A1:A1654=DATEVALUE(now()+6))+('All Events'!A1:A1654=DATEVALUE(now()+7)))

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31300

A starting point would be to look at the three FILTER functions.

  • FILTER
  • SORT
  • UNIQUE

Here is the link to the list of Google Spreadsheet functions:

Google Support - Spreadsheet Function List

I'm sure there is a way, using functions, to filter the data you want. If you want something more automated, you'd need to program something. That would be more work.

Either way, it's possible.

Upvotes: 0

Related Questions