Need a Script to check if rows need to be inserted into a sheet

[Ayan Basak]

• Get the total length of a tab on a sheet

• Iterate over the sheet and get the number of rows with data

• If the difference is less than a particular number (say 50 or 100) - then a notification goes out to someone and asks them to insert more rows into the tab

• Set this script up for a trial sheet and then we can customize for all sheets that are part of our system

[Clark Lind]

Something simple like this should work. Please edit to tailor the content to your needs.

function addRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();  //get the spreadsheet
var ws = ss.getSheetByName("YOUR_SHEET_NAME"); //get the sheet (or "tab")
var url = `${ss.getUrl()}#gid=${ws.getSheetId()}`; //build the url to the specific tab
var neededRows = 20; //set to the number you want the email to trigger on
var rowGap = ws.getMaxRows() - ws.getLastRow(); //get the difference between total rows and the last data row

//if the gap is less than what is required, send the email
  if (rowGap < neededRows) {

    var email = "em...@gmail.com"; //set to correct address to receive email
    var subj = "Sheet needs more rows added"; //set to the subject you want
    var msg = `Hey! the spreadsheet needs more rows added!<br>
                So please add them <a href="${url}">here</a><br>
                Thanks bunches! xoxo`; //edit the message to your liking ;)

//note: the email will be coming "from" whoever runs the script or sets up a trigger for the script to run
    MailApp.sendEmail({  
        to: email,
        subject: subj,
        htmlBody: msg

         })
   }
}

[Ayan Basak]

Thank you very much for your help.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/7W9fJ-NNRKg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4f462ff4-f2eb-4040-8e6c-2e348c30b572n%40googlegroups.com.

[Ayan Basak]

I am getting this error after putting the url ID

[Brett Grear]

You already have the Spreadsheet in your variable ss.

You don't need to provide the Id as well.

Just use:

ss.getUrl()

instead of ss.getUrl('id')

[Brett Grear]

Also the same for Sheet Id

Should be ws.getSheetId() instead of including the id 

[Ayan Basak]

Thanks a lot!!!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6adc09b6-9c68-4a34-b373-17702b21ee9dn%40googlegroups.com.