so this is just a steps for this project. I will explain the full project first, and maybe you can suggest another solution.

Basically, I have a template sheet for my job (Staffing Sheet). The sheet is for staffing purposes. When we scan an Employee's badge, their id populates, and the cell beside it has a formula, where their name shows up. Then a few cells to the right of that cell (where their name populates), it shows their role for the day(this is a hidden cell that will not show in the screenshot. I do this to minimize the congestion in the sheet). I want to create another sheet template (I'll call this one Sort Sheet) that automates data transfer and sorting from daily Staffing Sheets to a Sort Sheet. The Sort Sheet should automatically update with sorted employee data from the current Staffing Sheet, without manual formula changes.

For this step, I want to create a sheet template that allows me to pull data from whatever sheet I choose. I have to create a new staffing sheet everyday. I want the Sort Sheet to pull cells from the new staffing sheet that I create.

I don't want to create a Sort Sheet that makes me change all of the formulas in order to grab from the new sheet.

I tried to have cell A1 as the name of the new Staffing Sheet, and tried to have the formulas look like this: =A1!(pick a formula) assuming that it was going to try to pull data from a sheet title that I would put in cell A1. However, this did not work.

I tried my best to describe my issues, but it is difficult to explain my issue over text. Please reach out if you have questions.

so this is just a steps for this project. I will explain the full project first, and maybe you can suggest another solution.

Basically, I have a template sheet for my job (Staffing Sheet). The sheet is for staffing purposes. When we scan an Employee's badge, their id populates, and the cell beside it has a formula, where their name shows up. Then a few cells to the right of that cell (where their name populates), it shows their role for the day(this is a hidden cell that will not show in the screenshot. I do this to minimize the congestion in the sheet). I want to create another sheet template (I'll call this one Sort Sheet) that automates data transfer and sorting from daily Staffing Sheets to a Sort Sheet. The Sort Sheet should automatically update with sorted employee data from the current Staffing Sheet, without manual formula changes.

For this step, I want to create a sheet template that allows me to pull data from whatever sheet I choose. I have to create a new staffing sheet everyday. I want the Sort Sheet to pull cells from the new staffing sheet that I create.

I don't want to create a Sort Sheet that makes me change all of the formulas in order to grab from the new sheet.

I tried to have cell A1 as the name of the new Staffing Sheet, and tried to have the formulas look like this: =A1!(pick a formula) assuming that it was going to try to pull data from a sheet title that I would put in cell A1. However, this did not work.

I tried my best to describe my issues, but it is difficult to explain my issue over text. Please reach out if you have questions.

• google-sheets
• google-sheets-api
• spreadsheet

• 2 Welcome to StackOverflow! Please provide a minimal reproducible example in the form of markdown tables containing your sample input data and desired output. You may create one with the help of this link. – leylou Commented Mar 14 at 21:15
• 1 Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 15 at 0:11
• daily Staffing Sheets to a Sort Sheet Are the daily "Staffing" sheets in the same spreadsheet file? Are you proposing that the "Sort" sheet would in the same spreadsheet file, or a separate file? What are the names of the daily sheets? Does the sheet containing the daily sheet relate to a given period of time (say a week or month, 4 week period, etc). This info should be included in the body of the question and in the MRE. – Tedinoz Commented Mar 15 at 2:20
• I have to create a new staffing sheet everyday. Are all these sheets contained in a given folder? I want the Sort Sheet to pull cells from the new staffing sheet that I create. How do you understand that the "Sort" sheet would know the name/existence of the new staffing sheet? – Tedinoz Commented Mar 15 at 2:22

1 Answer 1

You want to import the data from any given "Daily" spreadsheet to a "Sort" sheet. This requires several elements:

• a folder to contain the "Daily" sheets
• a script to create a list of the "Daily" sheet names and their url
• a Data validation (dropdown) cell from which a "Daily" sheet can be selected.
• an IMPORTRANGE formula that imports the data from the given "Daily" sheet.

Consider this approach:

• create a folder on Google Drive to hold the "Daily" sheets
  • note the FolderID
• create a "Sort" sheet;
  • save the sheet wherever is appropriate except do NOT save it to the "Daily" sheet folder.
  • create a sheet in "Sort" and name it "references"
    • enter "Name" in cell A1, and "URL" in cell B1
  • copy the listSheets() function (details below) to the "Sort" Sheet editor
    • insert the FolderID in the script and save
  • run listSheets manually and confirm that the sheet names and URLs are updated on "references".
  • create an onOpen() trigger for listSheets so that the list of sheet names/urls is updated every time the "Sort" sheet is opened.
  • on "Sheet1" (or whichever sheet you may choose), create a Data validation dropdown in cell B1.
    • Criteria = "Dropdown (from a range)"
    • Range = =references!A2:A
  • insert this formula in Cell A3 =IMPORTRANGE(VLOOKUP(B1,references!$A$2:$B,2,0),"Sheet1!A1:Z") where "Sheet1!A1:Z" corresponds to the relevant data range on a "Daily" sheet.

Import data from a given sheet

• use the dropdown to select a "Daily" sheet
  • because listSheets runs whenever "Sort" is opened, the dropdown will show ALL the "Daily" sheets in the folder (including any newly created sheets).
• data should populate in the range where A3 is the top left corner.
• if cell A3 shows #REF, then you may need to "Allow Access" to the spreadsheet.

function listSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var refSheetName = "references"
  var refsheet = ss.getSheetByName(refSheetName)

  var folderId = "<<insert "Daily" sheets folderID>>"
  // get a list of files in the folder
  var folder = DriveApp.getFolderById(folderId)
  var files = folder.getFiles()

  // great an temporary array to hold file names
  var tempList = []

  // loop though the files, get each name and URL
  // and push onto the temporary Array
  while (files.hasNext()) {
    const file = files.next();
    tempList.push([file.getName(),file.getUrl()])
    // Logger.log("DEBUG: file name: "+file.getName()+", url: "+file.getUrl()) 
  }
  //Logger.log(tempList) // DEBUG

  // sort the Temporary array
    tempList.sort(function(x,y){
      var xp = x[0];
      var yp = y[0];
      return xp == yp ? 0 : xp < yp ? -1 : 1;
    });
  //Logger.log(tempList) // DEBUG
  
  // paste the list of names and URLs to the references sheet
  refsheet.getRange(2,1,tempList.length,tempList[0].length).setValues(tempList)
}

Credit: Serge insas for the elegant Array sort routine

SAMPLE - "References" Sheet

SAMPLE - Dropdown