0

I have a Google Sheets document that keeps adding hundreds of rows and slowing down the project, and sometimes causing it to crash.

Is there a way to limit the number of rows to say 100. I do not want to remove all empty rows, because it is a log that constantly needs data added to so a new row is always needed. However, we only use around 100 rows for each document.

I am not sure if this is due to a script I have attached or if it is just part of google sheets.

   function setFormat() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
for (var i in sheets) {
    var MaxRows = sheets[i].getMaxRows();
    var MaxColumns = sheets[i].getMaxColumns();

sheets[i].getRange(2, 1, MaxRows, MaxColumns)
.setFontFamily('Arial')
.setFontSize('9')
.setFontStyle('normal')
.setFontColor('black')
.setVerticalAlignment('center')
.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);

sheets[i].getRange(1, 1, 1, MaxColumns)
.setFontFamily('Comfortaa')
.setFontSize('9')
.setFontStyle('bold')
.setFontColor('black')
.setVerticalAlignment('center')
.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);

sheets[i].autoResizeColumns(1, 1);
sheets[i].setColumnWidths(2, 1, 80);
sheets[i].setColumnWidths(3, 1, 75);
sheets[i].setColumnWidths(4, 1, 90);
sheets[i].setColumnWidths(5, 4, 45);
sheets[i].setColumnWidths(9, 3, 75);
sheets[i].setColumnWidths(12, 2, 130);
sheets[i].setColumnWidths(14, 1, 95);
sheets[i].setColumnWidths(15, 1, 100);
sheets[i].setColumnWidths(16, 1, 95);
sheets[i].setRowHeights(2, MaxRows-1, 15);
sheets[i].setRowHeights(1, 1, 16);   }}

and

   function dataAlignment1() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 1, lr, 2);
     var set = r.setHorizontalAlignment('left');
   }

   function dataAlignment2() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 3, lr, 1);
     var set = r.setHorizontalAlignment('right');
   }

   function dataAlignment3() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 4, lr, 1);
     var set = r.setHorizontalAlignment('left');
   }

   function dataAlignment4() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 5, lr, 4);
     var set = r.setHorizontalAlignment('left');
   }

   function dataAlignment5() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 9, lr, 2);
     var set = r.setHorizontalAlignment('right');
   }

   function dataAlignment6() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 11, lr, 1);
    var set = r.setHorizontalAlignment('center');
   }

   function dataAlignment7() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 12, lr, 4);
     var set = r.setHorizontalAlignment('left');
   }

   function dataAlignment8() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var r = s.getRange(2, 16, lr, 1);
     var set = r.setHorizontalAlignment('center');
   }

   function headerAlignment() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getActiveSheet();
     var lr = s.getLastRow();
     var lc = s.getLastColumn();
     var r = s.getRange(1, 1, 1, lc);
     var set = r.setHorizontalAlignment('center');
   }
3
  • 1
    Is there a way to limit the number of rows to say 100 No. Are you saying you want to delete every row beyond 100?
    – CodeCamper
    Dec 23 '19 at 0:02
  • Your question is very unclear and quite vague. Dec 23 '19 at 5:07
  • No, what I am saying is each morning I come back and there will be around 1000 rows. Everything over 100 is empty, I didn't add them. Dec 23 '19 at 6:11
0

I think you can control the number of rows inserted from the script. You can remove or append to the sheet. As much as I understood, I think you can save each 100 rows in a file or append the latest 100 row on the top of the file or remove the oldest row/save it in a different sheet and add the new row.

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.