CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Apfel007a
Apfel007a

Reputation: 11

Delete first row in gsheet and shift rows up

My google sheet, values are just an example

This is just a gsheet as an example for privacy reasons.

In my actual gsheet I am appending automatically data with python. But my table does not need to be longer than 50 rows. How can I automate in gsheet with JS that if row 51 is added row 1 gets deleted and all list entries shift up one row so that my data is in row 1 to 50? 

var ss = SpreadsheetApp.openById("XXXXX");
var sheet = ss.getSheetByName('Tabellenblatt1');


function myFunction() {

//if row number >50 exceute code below   

//first row should be deleted here


//shift rows up
sheet.getRange("A1:B3").moveTo(sheet.getRange("A2:B4"));

}

Upvotes: 1

Views: 790

Answers (2)

Apfel007a
Apfel007a

Reputation: 11

Thanks for the help. The code below works. I want to execute it via the trigger every minute so that there wont be much more rows than 50.

function deleteRows() {
  var ss = SpreadsheetApp.openById("XXXX");
  var sheet = ss.getSheetByName('Tabellenblatt1');
  //count rows
  var max_rows = sheet.getLastRow();
  var limit = 50;
  if (max_rows >limit)
  {
    //erste Zeile mit Inhalt soll gelöscht werden
    sheet.getRange('A2:B2').clearContent();
    //shift rows up
    sheet.getRange("A3:B").moveTo(sheet.getRange("A2:B"));
  }
}

Upvotes: 0

JSmith
JSmith

Reputation: 4808

I can't really see how you can handle that automatically as you are entering values in python so the onChange trigger won't work.

Still you can create a timebased trigger and "crop" the datas depending on the frequency of your inputs.

if you still need it the way you asked you can try this this will delete inside the range but not the entire row, meaning this won't affect the other datas. 

function myFunction() {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheet = ss.getActiveSheet();
    var limit = 50;
    var numRows = sheet.getLastRow();
    var range = sheet.getRange('A1:B' + numRows + '');
    var values = range.getValues();
    var numRowsToDelete = 0;
    if (numRows > limit){
     numRowsToDelete = numRows - limit;
     values.splice(0, numRowsToDelete)
     for (var i = 0; i < numRowsToDelete; i++)
     {
       var arr = [];
       for (var j = 0; j < values[0].length; j++)
         arr.push("");
        values.push(arr)
     }
     range.setValues(values);
    }
}

or even simpler if you want to delete the entire row you can do

function myFunction() {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheet = ss.getActiveSheet();
    var numRows = sheet.getLastRow();
    var limit = 50;

    if (numRows > limit){
     var numRowsToDelete = numRows - limit;
     //first argument is the start index where you delete so if you
     // have a header you can put 2 and add a + 1 to the limit variable
     sheet.deleteRows(1, numRowsToDelete);
    }
}

REFERENCE:

time based trigger

Upvotes: 1

Related Questions