I want to automatically add 'for example' 500 rows to the last rows in several selected sheets in google sheets using Google App script and this will be time-driven trigger every week. tried all script related to it here, none append the rows. there any App script that does that function?

CodePudding user response：

Here is an example how it could be done:

function add_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var sheets_indexes = [0,1,2]; // get the list of sheets by their indexes

  for (let i of sheets_indexes) {
    let sheet = sheets[i];
    let last_row = sheet.getLastRow() || sheet.getMaxRows();
    sheet.insertRowsAfter(last_row, 500);
  }
}

function set_trigger() {
  ScriptApp.newTrigger('add_rows').timeBased().everyWeeks(1).create();
}

Script takes list of sheets by their indexes and add 500 rows after the last row. With the function set_trigger you can set the time driven trigger that will run the script every week.

CodePudding user response：

Here's a way to do it by sheet names, set to a time-based trigger.

// By Name - Only Specified Sheets
function addRows() {

  const includeSheets = [`Sheet1`, `Sheet2`, `Sheet3`, `etc`]

  SpreadsheetApp.getActiveSpreadsheet().getSheets()
                .filter(sheet => includeSheets.includes(sheet.getName()))
                .forEach(sheet => {
                  sheet.insertRowsAfter(sheet.getLastRow(), 500)
                })

}

// By Name - Exclude Specified Sheets
function addRows() {

  const excludeSheets = [`Sheet1`, `Sheet2`, `Sheet3`, `etc`]

  SpreadsheetApp.getActiveSpreadsheet().getSheets()
                .filter(sheet => !excludeSheets.includes(sheet.getName()))
                .forEach(sheet => {
                  sheet.insertRowsAfter(sheet.getLastRow(), 500)
                })

}