Google Spreadsheets Script - Copy cell value to another column

Question

I have a spreadsheet which calculates my total bank savings across different accounts.

I would like to write a script which, when run, copies the total savings calculated that day to another column along with a timestamp. The idea is that I can then plot this data in a graph to see the trend in my savings plotted over time.

In practice, that means copying the value of A5 to the first empty row of column B and printing the date in the first empty row of column C.

Does anybody know how this can be done?

Thanks!

Have you tried any code. If yes, add that in question. – Narendra Jul 16 '13 at 04:09 — Narendra, Jul 16 '13 at 04:09

score 1 · Answer 1 · edited May 23 '17 at 11:58

Copy this into your spreadsheet script, and set it up as a daily trigger function.

function recordTodaysBalance() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var balance = sheet.getRange("A5").getValue();
  var nextRow = getFirstEmptyRow('B');
  // Record current balance and timestamp at end of columns B & C
  sheet.getRange(nextRow, 2, 1, 2).setValues([balance,new Date()]);
};

// From https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRow(columnLetter) {
  columnLetter = columnLetter || 'A';
  var rangeA1 = columnLetter + ':' + columnLetter;
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange(rangeA1);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1); // +1 for compatibility with spreadsheet functions
}

Adapted from this answer.

Google Spreadsheets Script - Copy cell value to another column

1 Answers1