# Scripts, scripts, scripts!

**URL:** <https://community.glideapps.com/t/scripts-scripts-scripts/18965>\
**Category:** Ask for Help\
**Created:** [November 26, 2020, 6:15pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965 "2020-11-26T18:15:21Z")\
**Posts on this page:** 1\
**Showing post:** 26

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [November 29, 2020, 12:42pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/26 "2020-11-29T12:42:25Z")

</div>

Here is another one that I use _all_ the time as an alternative to `ARRAYFORMULA`.  
The neat thing about this is that it works with _any_ formulas, whereas `ARRAYFORMULA` has some limitations.

Again, I didn’t write this one myself, and I can’t recall where I got it from (probably Stackoverflow), so I can’t take any credit for it. But I’ve been using it for 3 or 4 years and it’s never failed me.

What it does and how it works is described in the comments.

> **Summary**
>
> ```
> /**
> * Copy Formulas Down
> * This script copies functions from a source row (usually the first data row in a spreadsheet) to
> * rows at the end of the sheet, where the colums for these formulas are still empty.
> * It checks row from the bottom upwards, copying formulas into the rows where there are no formulas yet,
> * and stops as soon as it finds a row which has a formula already.
> * When copying formulas, it leaves values in other cells unchanged.
> */
> 
> /**
> * Copy formulas from the a source row in a sheet to rows below
> * @param {Sheet} sheet Google Spreadsheet sheet
> * @param {int} sourceRow (optional) 1-based index of source row from which formulas are copied, default: 2
> */
> function copyFormulasDown(sheet, sourceRow) {
> if (sourceRow === undefined) {
> sourceRow = 2;
> }
> var formulas = getFormulas_(sheet, sourceRow);
> if (formulas !== {}) {
> var rows = sheet.getDataRange().getFormulas();
> for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
> copyFormulas_(sheet, r, formulas);
> }
> }
> }
> /**
> * Copy formulas into row r in sheet
> * @param {Sheet} sheet Google Spreadsheet sheet
> * @param {int} r 1-based index of row where formulas will be copied
> * @param {array} formulas array of objects with column index and formula string
> */
> function copyFormulas_(sheet, r, formulas) {
> for (var i = 0; i < formulas.length; i++) {
> sheet.getRange(r + 1, formulas[i].c + 1).setFormulaR1C1(formulas[i].formula);
> }
> }
> /**
> * Read formulas from the source row, creating an array of objects
> * Each objects contains the column index and formula string
> * @param {Sheet} sheet Google Spreadsheet sheet
> * @param {int} r 1-based index of source row
> * @return {array} array of objects
> */
> function getFormulas_(sheet, r) {
> var row = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
> var formulas = [];
> for (var c = 0; c < row.length; c++) {
> if (row[c] !== '') {
> formulas.push({
> c: c,
> formula: row[c]
> });
> }
> }
> return formulas;
> }
> 
> ```

Edit: one thing I should point out is that if you’re adding rows then you need to run this every time a new row (or rows) get added. But that’s easy enough with a trigger.

---

_[View the full topic](https://community.glideapps.com/t/scripts-scripts-scripts/18965)._