Öffnen Sie Google Docs Spreadsheet mit Namen

Den DocsList service in den Antworten nicht mehr so funktioniert, wie Sie wurde abgeschrieben. Ich aktualisiert meine scripts Aussehen wie die folgende.

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

Ersatz für DocsList ist DriveApp https://developers.google.com/apps-script/reference/drive/drive-app

InformationsquelleAutor Dan

Die Art und Weise, dass ich tun etwas ähnliches, was Sie fordert, ist zunächst ein Skript machen Sie sich eine Kopie meiner Tabelle (welche nenne ich eine frozen-backup), ist immer zu groß. Sobald ich sicher die Kopie, dann habe ich das gleiche script löschen Sie alle diese Zeilen aus der too-big-Tabelle, die nicht mehr benötigt werden. (Ich glaube, dass wir mehrere eingefrorene sicherungen kostet das google-Konto alles, damit dies rentabel ist)

Beachten Sie, dass ich Zeilen löschen eins nach dem anderen, und das kostet Zeit. Ich mache dies, da ich nicht löschen Sie alle Zeilen unterhalb ein Punkt, aber nur bestimmte Zeilen, die mit einer Bedingung.

In meinem Fall habe ich eine weitere kleine Prozedur zusätzlich zu den oben genannten, die haben das Skript kopieren Sie alle Zeilen, über die ich löschen die in einer Dritten Tabelle (zusätzlich zu den gefrorenen backup), aber dies scheint mehr zu sein, dass Sie gefragt haben.

Dies ist mein code (beachten Sie, dass die Haupt-Tabellenblatt, in das Blatt, wir gehen zum entfernen von Zeilen aus, mit dem Namen 'Original' hat Eine Spalte als Zeitstempel für jede Zeile; die Zelle A1 wird als 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);
}

InformationsquelleAutor David Tew