CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jonolumb
jonolumb

Reputation: 111

Google Spreadsheets Script - Copy cell value to another column

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!

Upvotes: 1

Views: 17018

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

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.

Upvotes: 1

Related Questions