Open Google Docs Spreadsheet by name

Question

I have a situation where a script is taking input data and sending it to a spreadsheet. After a while, this spreadsheet becomes too big.

Right now we have to manually move the items from the the primary spreadsheet to a new one. The reason is that not everyone is familiar with the code and are willing to change the ID in the code.

I would like to know if there is a way to open the spreadsheet by name. If not, is there a better way of achieving what we need (described above)

Solution 1

The DocsList service used in one of the answers no longer functions as it has been depreciated. I updated my scripts to look more like the following.

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

The replacement for DocsList is DriveApp https://developers.google.com/apps-script/reference/drive/drive-app

Solution 2

Update: DocsList has now been sunset. Use DriveApp.getFilesByName(name) instead.

---

David has provided some good code for shuffling your data around. If all you really did need was just to open a spreadsheet by name then this will do the trick:

function getSpreadsheetByName(filename) {
  var files = DocsList.find(filename);

  for(var i in files)
  {
    if(files[i].getName() == filename)
    {
      // open - undocumented function
      return SpreadsheetApp.open(files[i]);
      // openById - documented but more verbose
      // return SpreadsheetApp.openById(files[i].getId());
    }
  }
  return null;
}

Solution 3

The way that I do something similar to what you are asking for is to first have a script make a copy of my spreadsheet (which I will call a frozen backup) that is getting too big. Once I safely have the copy, I then have the same script delete all those rows from the too big spreadsheet that are no longer required. (I believe that having multiple frozen backups does not cost the google account anything, so this is viable)

Note that I delete rows one by one; this takes time. I do this since I don't delete all the rows below a point, but only certain rows which match a condition.

In my case I do have another small procedure in addition to the above, which is to have the script copy all the rows that I am about to delete into a third spreadsheet (in addition to the frozen backup), but this seems to be more that you have asked for.

This is my code (note that the main spreadsheet, in the sheet we are going to remove rows from, named 'Original', has column A as Time Stamp for each row; the 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);
}

Comments

[user1807201]

Thanks for both suggestions. They are both useful for our purposes.

[Admin]

This answer is outdated now; the current approach is in another answer.

[Hsehdar]

Thanks, this helped.