Ouvrez Google Docs feuille de calcul de par leur nom

La DocsList service utilisé dans l'une des réponses ne fonctionne plus comme il a été amortis. J'ai mis à jour mes scripts pour ressembler à la suivante.

//Open the file
  var FileIterator = DriveApp.getFilesByName(FileNameString);
  while (FileIterator.hasNext())
  {
    var file = FileIterator.next();
    if (file.getName() == FileNameString)
    {
      var Sheet = SpreadsheetApp.open(file);
      var fileID = file.getId();
    }    
  }

Le remplacement de DocsList est DriveApp https://developers.google.com/apps-script/reference/drive/drive-app

OriginalL'auteur Dan

La façon que je fais quelque chose de semblable à ce que vous demandez est d'abord avoir un script de faire une copie de ma feuille de calcul (que je vais appeler une gelée de sauvegarde) qui devient trop grand. Une fois que je peux avoir la copie, puis je avoir le même script de supprimer toutes les lignes à partir de la trop grande feuille de calcul qui ne sont plus requis. (Je crois que le fait d'avoir plusieurs gelé les sauvegardes ne coûte pas le compte google de rien, donc c'est viable)

Remarque que je supprime les lignes une par une; cela prend du temps. Je fais cela depuis que je ne pas supprimer toutes les lignes ci-dessous un point, mais seulement certaines lignes qui correspondent à un état.

Dans mon cas, j'ai une autre petite procédure en plus de ce qui précède, ce qui est d'avoir le script de copie toutes les lignes que je suis sur le point de supprimer dans une troisième feuille de calcul (en plus de la gelée de sauvegarde), mais cela semble être plus que vous avez demandé.

C'est mon code (notez que la principale feuille de calcul, dans le tableau, nous allons supprimer des lignes de, nommée "Original", a la colonne A comme horodatage pour chaque ligne, la cellule A1 est appelé Temps de Timbre):

 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);
}

OriginalL'auteur David Tew