CODEWITHSUNDEEP
javascriptgoogle-sheetsspreadsheet
Simon
Simon

Reputation: 25

Google Sheets script: how to automatically copy down formulas each day

hope I can explain this succinctly. It's a doozy of a problem for someone with my low understanding of Sheets and scripts.

So I have a sheet using this script once a day:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.appendRow(values[0]);
  var cells = sheet.getRange("A2:A980");
  cells.setNumberFormat("dd/MM/yyyy");
};

It grabs data from another sheet and appends it to columns A, B, C (Date, amount, %). Columns D-G contain formulas tracking absolute and % changes in B & C over time. At present every day I go and manually copy down those formulas, but there must be a way to automate that? Presumably possible methods include the following:

1) add something to the script to copy down the formula from the previous day. This guy has written something that may help, but I know so little about coding I can't even understand how to integrate this into my present script: http://googlescripts.harryonline.net/copy-formulas-down

2) edit the script to append to Columns A-C, ignoring any content in other columns. Then I could fill down the formulas in D-G (using IF(isblank...)) and leave them there. However if I do that now, the script appends new data in a new row at the very bottom, after all the content in columns D-G.

3) edit the script and use an array formula? I finally worked out an array formula that works on its own terms. But sadly the script still functions just like in problem 2 above. i.e. it appends new data in a new row at the very bottom of the spreadsheet, perceiving all existing rows to be filled by the array formulas, even when what they're returning is blank cells.

Any ideas on how to make any of the above a reality, or any completely different solutions, all massively appreciated! Cheers

Upvotes: 1

Views: 3998

Answers (1)

sebbtornquist
sebbtornquist

Reputation: 41

My understanding is this.

You want the values you get from another sheet to be pasted on the first empty row of column A (because if A has values then B and C has values too). And this should also be done once a day automatically.

function getFirstEmptyRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('History');
  var values = sheet.getRange('A:A').getValues();
  var firstEmptyRow = values.filter(String).length +1; // filter out all non-string entries and get its length+1 (first empty row)

  return firstEmptyRow
}

This function returns the first empty row as an integer (number) of column A.

Your new code should look like this

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  // sheet.appendRow(values[0]);
  var firstEmptyRow = getFirstEmptyRow();
  sheet.getRange(firstEmptyRow, 1, 1, values[0].length).setValues(values);
  var cells = sheet.getRange("A2:A980");
  cells.setNumberFormat("dd/MM/yyyy");
};

function getFirstEmptyRow() {
  var sheet = SpreadsheetApp.getActiveSheet().getSheetByName('History');
  var values = sheet.getRange('A:A').getValues();
  var firstEmptyRow = values.filter(String).length +1; // filter out all non-string entries and get its length+1 (first empty row)

  return firstEmptyRow
}

For the once a day automatically problem.

To create a trigger through a dialog in the script editor, follow these steps:

  1. From the script editor, choose Edit > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger. (which should be Record history)
  4. Under Events, select Time-driven
  5. Select one which is trigged on a daily basis and at which hour of the day you would like it to trigger.

The script will the automatically run the function you specified once a day at the hour interval you specified.

The script will look for the first empty row of column A and set the values of column A, B and C of that row to whatever is in 'values[0]'.

This would also allow you to just fill the remaining columns with whatever formula you want without them being overwritten or making values append to the bottom of the sheet.

Beware though that the getFirstEmptyRow function I specified (which I use) is fast but rather dumb. It does not take into consideration if there is an empty row in the middle for example. This will make it overwrite the last entry (if there is only one gap).

If there's always a value in column A then this is not an issue, if there are gaps use the code below.

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  // sheet.appendRow(values[0]);
  var firstEmptyRow = getFirstEmptyRow();
  sheet.getRange(firstEmptyRow, 1, 1, values[0].length).setValues(values);
  var cells = sheet.getRange("A2:A980");
  cells.setNumberFormat("dd/MM/yyyy");
};

function getFirstEmptyRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('History');
  var values = sheet.getRange('A:A').getValues();
  var valuesLength = values.length;
  var i;
  var firstEmptyRow;

  for (i = 0; i < valuesLength; i++) {
    if (values[i] != '') {
      firstEmptyRow = i +1 // Because index starts from 0 and not 1
    }
  }

  return firstEmptyRow + 1 // +1 because the above forloop assigns the last row as the last row containing a value
}

Upvotes: 1

Related Questions