Google Spreadsheet - Scripts, copy cell from one sheet to another sheet EVERYDAY at a specific time

0
votes

I know i might be asking the same thing. But i tried the scripts in different answers (obviously changing the cell value) but my question is bit different.

I need to make a script in Google Spreadsheet to copy cell from one sheet to another sheet (In the same document) EVERYDAY at a specific time.

I have attached images below showing the what cells to copy from and to. Some cells need a SUM formula before copying.

I have also shared the copy of google sheet for convenience. https://docs.google.com/spreadsheet/ccc?key=0AlVSjrM0ckyLdEtiLVNuOVpwQ3BGdUgwU0VpcldKaFE&usp=sharing

Images: Sheet1 and Sheet2 http://i59.tinypic.com/23jsu9x.jpg

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

You can update the fromTo array to include more ranges. Script is fairly self-explanatory. Set up trigger to run backup everyday. You might want to change GMT to your timezone.

function backup() {
  var src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Report");
  var dst = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly Production Report");
  var dstRow = getDstRow(dst);

  var fromTo = [["B26", 2],
                ["C26", 3],
                ["B29:B31", 4],
                ["C29:C31", 5],
                ["B34", 6],
                ["C34", 7]];

  for (var i=0; i<fromTo.length; i++) {
    var r = fromTo[i];
    dst.getRange(dstRow, r[1]).setValue(src.getRange(r[0]).getValues().reduce(sum, 0));
  }
}

function getDstRow(dst) {
  var today = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
  Logger.log(today);
  for (var row=1; row<=dst.getLastRow(); row++) {
    Logger.log(dst.getRange(row,1).getValue());

    try {
      var d = Utilities.formatDate(dst.getRange(row, 1).getValue(), "GMT", "yyyy-MM-dd");
      if (d == today) {
        return row;
      }
    } catch (err) {}
  }
}

function sum(a,b) {
  return a + b;
}