Google Sheets – Inverse of GetSpreadsheetNameById

google sheetsgoogle-apps-scriptgoogle-drive

This post Display sheet name in Google Spreadsheets shows how to get the name of a Google sheet, given its URL.

I have the opposite problem. I know the Spreadsheet name, but I want the URL to use in an importrange() call.

That is because I have a related group of Spreadsheets, one per week, and the week number is in the name of the Spreadsheet, so this week's Spreadsheet can work out the name of last week's Spreadsheet. I'm using importrange() to carry a value forward from last week's Spreadsheet into this week's Spreadsheet. But importrange() wants a URL.

It has been suggested (duplicate proposal) that this will solve my problem:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var url = ss.getUrl());

It won't. That tells me the URL of the active Spreadsheet. But I don't want the URL of the active Spreadsheet, I want the URL of a different Spreadsheet that I know only the name of.

Best Answer

If you know the file name exactly, You can use DriveApp to search the file and getUrl()

function getFile(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) { 
    var file = files.next();
    Logger.log('Name:'+file.getName()+'\nUrl'+ file.getUrl());
    return file.getUrl();
  }
}

If you don't know the name exactly, You can use DriveApp.searchFiles() method.

Related Solutions

Google-sheets – Script that adds new row to multiple sheets in Google Spreadsheets

Try this code:

function AddRow() {
  //gets the spreadsheet which it's bound to
  var ssheet = SpreadsheetApp.getActiveSpreadsheet();  

  //gets all sheets in this spreadsheet
  var sheet = ssheet.getSheets();                      

  //browses all the sheets, until "i" is larger than the number of sheets
  for (var i = 0; i < sheet.length; i++)               
  {
    //adds to the actual sheet a row AFTER the first row
    sheet[i].insertRowAfter(1);                        
  }
}

and trigger it on a daily basis (like on 1 a.m.)

Google-sheets – Automatically creating sheets across different Google Spreadsheets

This can be done with the following Google Apps Script. It should be added to the spreadsheet that receives form submissions (go to Tools > Script Editor). After entering the code (including the URLs of the spreadsheets A-D, E-L, etc), go to Resources > Current Project's Triggets and add a trigger "createSheet | From Spreadsheet | On form submit".

function createSheet(e) {
  var firstName = e.namedValues['First Name'];
  var firstInitial = firstName.slice(0,1).toUpperCase();
  var lastName = e.namedValues['Last Name'];
  var lastInitial = lastName.slice(0,1).toUpperCase();
  var ss;
  if (firstInitial >= 'A' && firstInitial <= 'D') {
    ss = SpreadsheetApp.openByUrl('url of your A-D sheet here');
  }
  if (firstInitial >= 'E' && firstInitial <= 'L') {
    ss = SpreadsheetApp.openByUrl('url of your E-L sheet here');
  }
  // ... and so on  
  ss.insertSheet(firstName + ',' + lastInitial);
}

The script assumes that your form has fields "First Name" and "Last Name". If these are named differently, change these strings in the code.

If the same student submits the form twice (or you have multiple students with the same First name + Last initial combination), the script will fail to create a new sheet, since one by that name already exists.

Best Answer

Related Solutions

Google-sheets – Script that adds new row to multiple sheets in Google Spreadsheets

Google-sheets – Automatically creating sheets across different Google Spreadsheets

Related Topic