Pasting a value into a new cell each time another is updated?

Question

I'm hoping to have 2 cells that update their value every week. Each time these cells get their values updated I would like them to also be printed onto another two cells on a different sheet. Each week these cells that they are printed onto moving down by one. For example Week 1, the two cells are printed in A1 and B1. Week 2 they are printed in A2 and B2, and so on.

I know how to do this in Excel but no idea how I can change that over to Google Sheets / Scripts.

dzorug dzorug · Accepted Answer · 2015-10-22T12:51:22

Assumptions:

Your new inputs are in input!A1 and input!B1; and
Your outputs will by in output!A and output!B.

You can write a script by clicking on Tools on the menu and entering Script Editor.

Then write this function:

function writeData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var input = ss.getSheetByName("input")
    .getRange(1, 1, 1, 2)
    .getValues();
  var outputSheet = ss.getSheetByName("output");
  var numRows = outputSheet.getLastRow();

  outputSheet.getRange(numRows + 1, 1)
    .setValue(input[0][0]);
  outputSheet.getRange(numRows + 1, 2)
    .setValue(input[0][1]);
}

Next, set up a trigger. Within Script Editor, under Resources, select Current project's triggers and then set up your trigger. If you want it to run weekly, use a time-driven trigger. You can also set a spreadsheet-driven trigger to run on every edit. Save everything.

If you don't want to use automatic triggers, you can execute the writeData() function by adding an item on the menu bar of the spreadsheet:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Write data", functionName: "writeData"});
  ss.addMenu("Custom functions", menuEntries);
}

Save, and if you refresh the spreadsheet you'll see a new item on the menu bar.

Pasting a value into a new cell each time another is updated?

1 Answers