Linking and Automatizing Multiple Google Forms, and Sheets. Pt. 1: Employer’s Side

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. Also, considering that I never worked with the google environment made it so much harder because I was just learning as I went through.

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))," ", ""),":", ""),"/","") ) ) )

Code above names the column “JobID” and adds the time of the submission after the 3-letter word, removing any 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:

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 in 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.