UpdateRows overwrites cell formula with formula output.

[Alexbetrayer]

Not sure if intended for simplicity, but currently updateRows() overwrites the formula in a cell with the value it outputs instead of leaving the old formula. Would be cool if it left the old formula.

First time writing an issue for anything, so forgive me if I'm putting too much.

I'm guessing it's from this:

getRows(): RowObject[] {
    const sheetValues = this.getValues(); // This right here

    return this.whereFn ? sheetValues.filter(this.whereFn) : sheetValues;
  }

Where this.getValues() calls sheet.getDataRange().getValues():

getValues() {
    if (!this._sheetValues) {
      const zh = this.headingRow - 1;
      const sheet = this.getSheet();

      if (!sheet) {
        return [];
      }

      const rowValues = [];
      const sheetValues = sheet.getDataRange().getValues();  // Right here
      const numCols = sheetValues[0] ? sheetValues[0].length : 0;
      const numRows = sheetValues.length;
      const headings = (this._sheetHeadings = sheetValues[zh] || []);

      for (let r = 0; r < numRows; r++) {
        const obj = { __meta: { row: r + 1, cols: numCols } };

        for (let c = 0; c < numCols; c++) {
          // @ts-expect-error: Headings are set already above, so possibility of an error here is nil
          obj[headings[c]] = sheetValues[r][c]; // @ts-ignore
        }

        rowValues.push(obj);
      }

      this._sheetValues = rowValues;
    }

    return this._sheetValues;
  }

Which in turn range.getValues() returns:
"Returns a two-dimensional array of values, indexed by row, then by column. The values may be of type Number, Boolean, Date, or String, depending on the value of the cell."
But does not return the formula in any case. You'd need to call range.getFormulas() for that.

range.getFormulas() returns a 2d array of the same size too, so it might be easy to add in the saving formula functionality in more or less the same way we iterate over values and assign them to a heading, possibly even in the same loop.

I'm still brainstorming it. I'm cool with opening a pull request for this, but I'm a bit new to helping on libraries.

[vlucas]

Ah, nice catch. Yes, this would be good to handle. I will have to add some more code for this and release a new version.

[Dmytro2V]

Also met this issue.
It is quite impossible to use writing when there are formulas. As this all is about spreadsheets, this happens often.

One way to fix it is do with getFormulas(). As this returns null when not a formula, we need to read data twice. Once with getFormulas() and second with getValues(). Then merge non-null formulas to values.
This is quite easy, but doubles a reading, and is a bit invasive to code. So I didn't dare to propose an update.
But instead I've proposed another approach.

Usually we don't need to update formulas on a sheet. We want them to be safe, while we updating other data.
So I've simply add a new method, patchRows(). It works like updateRows(), but write only in columns updated.
Name is like in HTTP terms - 'put' is when overwriting data, 'patch' when modifying only part of it.
Created a pull request. Hoping @vlucas will correct it and accept.
Do need this for my work.