Google-sheets – Posting more than 2,000 lines from IFTTT to a Google Sheets

google sheetsif-this-then-that

I have an IFTTT trigger set up whose action is to add a row to a Google Sheets. Once the spreadsheet reaches 2,000 rows, IFTTT creates a new document with the title: Document Title (1) instead of adding a new row to the original spreadsheet. I've manually added ~40,000 more rows to the original document, but IFTTT still adds them to the Document Title (1) document.

I'd like to have all the rows added to just one document.

How can I accomplish this?

Best Answer

Unfortunately, this is actually an issue on Google's end. There is a limit of 400,000 cells, (source) or 2 million cells (for the new Google Sheets) per spreadsheet. (source).

When IFTTT reaches this limit it makes a new spreadsheet.

One temporary way to get around this limit is by deleting the columns you don't need (for example columns E onwards). This decreases the number of cells you are using of your 400,000 / 2 million limit. But this only helps until you reach the new limit of rows.

If your sheet is still an old version another possible solution is making it a "new version sheet"

Once IFTTT decides to create a new spreadsheet all new data will be added to the new one, regardless of if you have added 'extra space'

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

var ss isn't necessary, since you don't use it in the script.
you need the row of the active cell, compared to the last row (getMaxRows)
add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference

Google-sheets – How to change default row height of a migrated Google Spreadsheet

At this time it's not possible to change the default row height. One workaround is to use Google Apps Script to change the row height. You could use on change installable trigger to call the script every time a new sheet or row is added.

Note:
If the cell includes breaklines, or it's wrapping setting is set to wrap it's not possible to adjust the row height to a height less than the required to by its content. See https://issuetracker.google.com/issues/36765452

Best Answer

Related Solutions

Google Sheets – Inserting Rows and Adding Cell References

Code

Remarks

Example

Google-sheets – How to change default row height of a migrated Google Spreadsheet

Related Topic