The way I am doing something similar to what you are asking is to first create a script copy of my table (which I will call a frozen backup) that gets too big. After I have successfully provided the copy, I will have the same script to delete all these rows from the oversized spreadsheet that are no longer required. (I believe having multiple frozen backups will not work in your google account, so this is viable)
Note that I delete the lines one by one; it takes time. I do this because I am not deleting all lines below the dot, but only certain lines that match the condition.
In my case, I have another small procedure in addition to the above, which is to make the script copy all the rows that I'm going to delete to the third table (in addition to the frozen backup), but this seems to be more. what did you ask for.
This is my code (note that the main table on the sheet that we are going to remove from the row called “Original” has column A as the timestamp for each row, cell A1 is called Time Stamp):
function ssCopy() { var id = "0ArVhODIsJ2.... spreadsheet key of the master spreadsheet"; var smsSS = SpreadsheetApp.openById(id); var recordingSS = SpreadsheetApp.openById("0AvhOXv5OGF.... spreadsheet key of archive spreadsheet");// you probably wont be using this var recordingSMSCopiesSheet = recordingSS.getSheets()[0]; var outgoingSMSsheet = smsSS.getSheetByName("Original"); outgoingSMSsheet.getRange("A1").setValue("Time Stamp"); var startRow = 2; var numRows = outgoingSMSsheet.getDataRange().getLastRow(); var numCols = 13; var dataRange = outgoingSMSsheet.getRange(startRow, 1, numRows, numCols); var objects = getRowsData(outgoingSMSsheet, dataRange); // Create one JavaScript object per row of data. var rowDataNumberArray = []; var rowToDeleteIndex = []; for (var i = 0; i < objects.length; ++i) { // Get a row object var rowData = objects[i]; if( Date.parse(rowData.timeStamp) > Date.parse(ScriptProperties.getProperty('lastDate')) && (rowData.done == 1) ){ //these are not used if these same 2 lines are inserted instead of here, downbelow var rowStuff = [] rowToDeleteIndex.push(i); for(n in objects[i]){ rowStuff.push( objects[i][n] )} rowDataNumberArray.push( rowStuff )}; Logger.log("rowData.number1 = " + rowStuff); } Logger.log(" rowDataNumberArray ~ " + rowDataNumberArray); if(rowDataNumberArray.length > 0) { for(row in rowDataNumberArray) {recordingSMSCopiesSheet.appendRow(rowDataNumberArray[row]);} } spreadsheetFrozenBackup(id) for( var i = rowToDeleteIndex.length-1; i >= 0; i-- ) //backwards on purpose outgoingSMSsheet.deleteRow(rowToDeleteIndex[i]+ 0 + startRow); //so we don't have to re-calculate our row indexes (thanks to H. Abreu) } ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// function spreadsheetFrozenBackup(id) { // Get current spreadsheet. var ss = SpreadsheetApp.openById(id); // Name the backup spreadsheet with date. var bssName = " Frozen Spreadsheet at: " + Utilities.formatDate(new Date(), "GMT+1:00", "yyyy-MM-dd'T'HH:mm:ss") + " : " + ss.getName() ; var bs = SpreadsheetApp.openById((DocsList.copy(DocsList.getFileById(ss.getId()), bssName)).getId()); } ////////////////////////////////////////////////////////////////////////////////////////// // // The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects' // tutorial. // ////////////////////////////////////////////////////////////////////////////////////////// // getRowsData iterates row by row in the input range and returns an array of objects. // Each object contains all the data for a given row, indexed by its normalized column name. // Arguments: // - sheet: the sheet object that contains the data to be processed // - range: the exact range of cells where the data is stored // - columnHeadersRowIndex: specifies the row number where the column names are stored. // This argument is optional and it defaults to the row immediately above range; // Returns an Array of objects. function getRowsData(sheet, range, columnHeadersRowIndex) { columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1; var numColumns = range.getEndColumn() - range.getColumn() + 1; var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns); var headers = headersRange.getValues()[0]; return getObjects(range.getValues(), normalizeHeaders(headers)); } // For every row of data in data, generates an object that contains the data. Names of // object fields are defined in keys. // Arguments: // - data: JavaScript 2d array // - keys: Array of Strings that define the property names for the objects to create function getObjects(data, keys) { var objects = []; for (var i = 0; i < data.length; ++i) { var object = {}; var hasData = false; for (var j = 0; j < data[i].length; ++j) { var cellData = data[i][j]; if (isCellEmpty(cellData)) { continue; } object[keys[j]] = cellData; hasData = true; } if (hasData) { objects.push(object); } } return objects; } // Returns an Array of normalized Strings. // Arguments: // - headers: Array of Strings to normalize function normalizeHeaders(headers) { var keys = []; for (var i = 0; i < headers.length; ++i) { var key = normalizeHeader(headers[i]); if (key.length > 0) { keys.push(key); } } return keys; } // Normalizes a string, by removing all alphanumeric characters and using mixed case // to separate words. The output will always start with a lower case letter. // This function is designed to produce JavaScript object property names. // Arguments: // - header: string to normalize // Examples: // "First Name" -> "firstName" // "Market Cap (millions) -> "marketCapMillions // "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored" function normalizeHeader(header) { var key = ""; var upperCase = false; for (var i = 0; i < header.length; ++i) { var letter = header[i]; if (letter == " " && key.length > 0) { upperCase = true; continue; } if (!isAlnum(letter)) { continue; } if (key.length == 0 && isDigit(letter)) { continue; // first character must be a letter } if (upperCase) { upperCase = false; key += letter.toUpperCase(); } else { key += letter.toLowerCase(); } } return key; } // Returns true if the cell where cellData was read from is empty. // Arguments: // - cellData: string function isCellEmpty(cellData) { return typeof(cellData) == "string" && cellData == ""; } // Returns true if the character char is alphabetical, false otherwise. function isAlnum(char) { return char >= 'A' && char <= 'Z' || char >= 'a' && char <= 'z' || isDigit(char); } // Returns true if the character char is a digit, false otherwise. function isDigit(char) { return char >= '0' && char <= '9'; } // setRowsData fills in one row of data per object defined in the objects Array. // https://developers.google.com/apps-script/storing_data_spreadsheets // For every Column, it checks if data objects define a value for it. // Arguments: // - sheet: the Sheet Object where the data will be written // - objects: an Array of Objects, each of which contains data for a row // - optHeadersRange: a Range of cells where the column headers are defined. This // defaults to the entire first row in sheet. // - optFirstDataRowIndex: index of the first row where data should be written. This // defaults to the row immediately below the headers. function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) { var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns()); var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1; var headers = normalizeHeaders(headersRange.getValues()[0]); var data = []; for (var i = 0; i < objects.length; ++i) { var values = [] for (j = 0; j < headers.length; ++j) { var header = headers[j]; values.push(header.length > 0 && objects[i][header] ? objects[i][header] : ""); } data.push(values); } var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), objects.length, headers.length); destinationRange.setValues(data); }