Hey, this is a project that I have done here at NC State, in the financial aid and scholarships department.
The objective was simple at first but got more challenging as I worked on it.
The project is for work-study applications where employers fill out a form providing the school with job opportunities, and the students fill out a form applying to those jobs. All the data is stored in google sheets.
Starting with the Employer’s Side:
They first fill out a form where they provide some basic information about the job they’re providing.
In my form I ask for their email, location, primary point of contact (PPC) name, PPC phone number, job title, select one of the categories provided, job description, number of positions, number of hours per week expected, time period for when the student can work (days, evenings, weekends), the pay rate, and other job specifics.
These questions are open to change, but I recommend having a set of categories since it would be chaotic otherwise.
Job ID’s are crucial throughout this process because that’s how we’ll identify each job. In order to create IDs I use two columns.
First:
=ArrayFormula( IFS( ROW(A:A)=1, "UniqueID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, UPPER(LEFT(C:C,3)) ) )
This names the column “UniqueID” and creates a 3 letter word with the first 3 letters of the company. C:C is where the company’s name is.
Then:
=IFERROR(ArrayFormula( IFS( ROW(A:A)=1, "JobID", LEN(A:A)>1, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT(P:P, LEFT(A:A,18))," ", ""),":", ""),"/","") ) ) )
This names the column “JobID” and adds the time of the submission after the 3 letter word, removing any of the symbols. The time of submission is automatically stored in the A:A column. “P:P” represents the 3 letter word we just created.
After storing these values in the responses sheet what I prefer doing is splitting them up by their categories in the proceeding columns.
I use:
=IFERROR(ARRAYFORMULA( IFS( ROW(A:A) = 1, "Category", LEN(A:A) > 1, "Category Name" )))
This fills a whole column with the category so it’s easy to distinguish between categories.
After this, I get every answer I need and put it next to their categories.
I use:
=ArrayFormula( IFERROR(IFS( ROW(A:A)=1, "Answer to the Question", R:R = "-", "", H:H = "Category", C:C )) )
“C:C” is where we want to search in the google sheet for the answer.
I do this for every category and that’s that google sheet done.
After those are sorted out in one google sheet, it’s time to distribute those to their own sheets.
In the folder that I’m working in, make a new folder inside that folder and name it “Position Google Sheets”
Create new sheets for each category and name it what the category’s name is.
In there I use:
=filter(IMPORTRANGE("Google sheets where the answers are", "The tab's name in the sheet !the column letter:the column letter"), IMPORTRANGE("Google sheets where the answers are", "The tab's name in the sheet !the column letter:the column letter")<>"")Do this for every answer that you picked, since you separated the categories in the original google sheet it should be easy to just get those column letters.
Once you get all the answers, start making new columns. Our new objective is to create google documents for each job and store them in the same sheet.
Create 5 columns and name them “Document Links”, “Category”, “Total Jobs”, “AVAILABLE JOB”, “AVAILABLE DOCUMENT”
Most of these columns are there just to make understanding better.
We’ll talk about “Document Links” the last, because it’s the most complicated one.
“Category” is basically the category repeating to create a separation column.
I use:
=IFERROR((arrayformula( ifs( ROW(A:A) = 1 , "Category", LEN(A:A) > 1 , "Category Name" ))))
for “AVAILABLE JOBS”
I use:
=ArrayFormula( IF(ISBLANK(A:A),IFERROR(0/0),"AVAILABLE JOB") )
which just prints out AVAILABLE JOB if there’s a job in that row
for “AVAILABLE DOCUMENTS”
I use:
=ArrayFormula( IF(ISBLANK(J:J),IFERROR(0/0),"AVAILABLE DOCUMENT") )
which checks the document column (J:J), and prints “AVAILABLE DOCUMENT” if there’s anything in that cell.
“Total Jobs” basically counts how many jobs there are in the sheet
to do that I use this line in the 2nd row:
=COUNTIF(N2:N, "AVAILABLE DOCUMENT")
and name the first row “Total Jobs”
I just check the document checker and count it. Separating this into 2 columns in my opinion makes it clearer, but you can choose to combine them into 1 line of code.
Now, the complicated part. To get the documents’ links to that column we’re gonna use Apps Script.
Before we start coding we need a prototype google doc to change its answers.
This is what my prototype looks like, but yours can look different based on the questions.
After this, we need a place to store the google docs that we’re going to create so create a new folder for each category.
Now the coding part.
After opening App Script -wherever it is they change its location every week…,- we can type:
function createNewGoogleDocs(){
//end bit of the url of doc template
const googleDocTemplate = DriveApp.getFileById('the end bit of the link');
//where docs end up in
const destinationFolder = DriveApp.getFolderById('the end bit of the link');
//where data is taken, change the sheet name for other sheets
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('the name of the google sheet');
const rows = sheet.getDataRange().getValues();
//loop through each row and get values
rows.forEach(function(row, index){
if (index === 0) return;
if (row[9]) return;
if (!row[12]) return;
if (row[13]) return;
const copy = googleDocTemplate.makeCopy(`${row[0]}, ${row[2]}`, destinationFolder)
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
//replace texts in the doc with the values
body.replaceText('{{INSERT CATEGORY}}', row[10]);
body.replaceText('{{INSERT DEPARTMENT}}', row[0]);
body.replaceText('{{INSERT LOCATION}}', row[1]);
body.replaceText('{{INSERT JOB TITLE}}', row[2]);
body.replaceText('{{INSERT JOB DESCRIPTION}}', row[3]);
body.replaceText('{{INSERT HOURS}}', row[4]);
body.replaceText('{{INSERT TIME FRAME}}', row[5]);
body.replaceText('{{INSERT PAY}}', row[6]);
body.replaceText('{{INSERT OTHER}}', row[7]);
body.replaceText('{{INSERT ID}}', row[8]);
//save changes !! important
doc.saveAndClose();
//get the url of the new doc created
const url = doc.getUrl();
//put that url into the sheet
sheet.getRange(index + 1, 10).setValue(url);
})
}Just by reading the comments in the code, this should be explanatory, but to explain it furthermore:
After we get the things we’re going to use; the prototype doc, the destination folder, the google sheet where we’re getting the data, we can start to change the answers from the prototype doc.
We loop through the google sheet first and get the data, however we don’t want to replace a link when it’s there that why we use “ if (row[9]) return;” 9 representing where the index where the link ends up in. “ ” if (!row[12]) return;” does the same thing.
if (index === 0) return;” checks if there’s any job there to begin with. “ if (!row[12]) return; ” does basically the same thing.
after we’re sure that we want a link there we start replacing the answers with our answers.
We save the code after these changes and get the document’s URL. We place the URL to the desired column in the sheet.
After this process, the whole sheet should look something like this.
Repeat this process for every category.
That should be all for the employer’s side.