Google Apps Script: Add automation for Homepage Summary spreadsheet

[macho-catt]

Dependency

• [x] #2901
• [x] #4134
• [x] #3641

Overview

We need an automated homepage summary spreadsheet to help PMs in identifying which wins entries show up in the website homepage randomizer.

Action Items

• [ ] Follow sections 1-3 in the Wins Apps Script Development Process[^4]. Note that in this issue you will only be modifying the Apps Script wins-form-responses so all commands should be run from /google-apps-script/wins-form-responses
• [ ] As described in section 3a and 3b, you will provide a dev lead with the google account you wish to use for testing, and request the dev lead to:
  • [ ] share the Wins Form Admin Guide[^1] as VIEWER
  • [ ] share the Wins-form (Response)[^2] sheet as VIEWER
  • [ ] share the Wins-form[^3]
    • [ ] Note to lead: make a copy of Wins-form, including the issue # in the name, then share that copy with the developer as EDITOR.
• [ ] Continue following instructions in 3c and d. As described in 3d, you will have to make modifications to certain documents in the test folder.
  • [ ] Update settings in Wins-form so that responses are stored in your test version of Wins-form (Response)
  • [ ] In Wins-form (Response), add trigger so that insertLatestFormSubmitIntoReviewSheet runs when the test form is submitted.
• [ ] Continue following instructions through section 6 "Editing". As described in this section, editing can be done locally in an IDE or in the Google Drive cloud editor, and the clasp utility can be used to transfer code back and forth between the local worksation and the Google Drive as needed.
• [ ] In Wins-form (Responses) sheet, copy the "Review" sheet, then rename the copy "Homepage Summary" and setup columns as in "Homepage summary prototype"
  • [ ] The following columns are formulas pulling values from the corresponding columns on the Review sheet: Display, Homepage, Manual Reviewed Role and should be setup or marked so that the user does not try to change the value from the "Homepage Summary" sheet.
  • [ ] Note that the formula for columns I through T is different in rows 2 and 3 from the remaining rows.
• [ ] From the Wins-form (Responses) sheet, click Extensions then Apps Script to view the code "winsFormResponse" and then create a fourth section that triggers on form submit
• [ ] Test the code so that on a new form submit, both the Review sheet and Homepage Summary sheet are updated appropriately. You will not have to run main() or create a PR or issue in GitHub.
• [ ] Follow all steps through section 8 "Completing an issue". For this issue, only a single file google-apps-script/wins-form-responses/Code.js should be committed.

Merge Team/dev Lead

• [ ] ensure that all access to Wins Form Admin Guide[^1], the Wins-form (Response) Sheet[^2], and the Wins-form[^3] have been revoked.

Resources/Instructions

• Google Apps Script
• _wins-data file
• wins page JS [^1]:Wins Form Admin Guide [^2]:Wins-form (Response) Sheet [^3]:Wins-form [^4]:Wins Apps Script Development Process

[github-actions[bot]]

Hi @macho-catt.

Good job adding the required labels for this issue. The merge team will review the issue and add a "Ready for Milestone" label once it is ready for prioritization.

Additional Resources:

• Wiki: How to read and interpret labels

[SAUMILDHANKAR]

Availability: 10 hrs per week. Tuesday and Thursday evenings. ETA: 1/10/22

[github-actions[bot]]

@SAUMILDHANKAR

Please add update using the below template (even if you have a pull request). Afterwards, remove the 'To Update !' label and add the 'Status: Updated' label.

1. Progress: "What is the current status of your project? What have you completed and what is left to do?"
2. Blockers: "Difficulties or errors encountered."
3. Availability: "How much time will you have this week to work on this issue?"
4. ETA: "When do you expect this issue to be completed?"
5. Pictures: "Add any pictures of the visual changes made to the site so far."

If you need help, be sure to either: 1) place your issue in the developer meeting discussion column and ask for help at your next meeting, 2) put a "Status: Help Wanted" label on your issue and pull request, or 3) put up a request for assistance on the #hfla-site channel.

_{You are receiving this comment because your last comment was before Monday, January 3, 2022 at 11:19 PM PST.}

[SAUMILDHANKAR]

Progress: Went over different sections of code.gs file. Will start creating the new section next and test adding data from review sheet to response sheet in personal copy and repo. Blockers: Need more analysis for the follwing column headers: Date Live on homepage start | Date Live on homepage end | # of times appeared on website Availability: 5 hrs ETA: 1/15/22

[SAUMILDHANKAR]

Progress: Didn't make much progress, since took a lot of days off last week. Blockers: Need more analysis for the following column headers: Date Live on homepage start | Date Live on homepage end | # of times appeared on website Availability: 5 hrs ETA: 1/30/22

[github-actions[bot]]

@SAUMILDHANKAR

Please add update using the below template (even if you have a pull request). Afterwards, remove the 'To Update !' label and add the 'Status: Updated' label.

1. Progress: "What is the current status of your project? What have you completed and what is left to do?"
2. Blockers: "Difficulties or errors encountered."
3. Availability: "How much time will you have this week to work on this issue?"
4. ETA: "When do you expect this issue to be completed?"
5. Pictures: "Add any pictures of the visual changes made to the site so far."

If you need help, be sure to either: 1) place your issue in the developer meeting discussion column and ask for help at your next meeting, 2) put a "Status: Help Wanted" label on your issue and pull request, or 3) put up a request for assistance on the #hfla-site channel.

_{You are receiving this comment because your last comment was before Monday, January 24, 2022 at 11:18 PM PST.}

[SAUMILDHANKAR]

Progress: Added a row on the homepage summary sheet with formulas to calculate respective number of positions (eg. engineers, designer, data analyst and so on) which have TRUE on the Homepage. Blockers: Will need help to access bot account for testing. Availability: 3 hrs ETA: 2/8/22

[SAUMILDHANKAR]

Progress: Identified an error in the response sheet, this might be related to the function insertLatestFormSubmitIntoReviewSheet. Will work on resolving this as similar function might be required for this issue. Blockers: No blockers right now. Availability: 3 hrs ETA: 2/15/22

[github-actions[bot]]

@SAUMILDHANKAR

Please add update using the below template (even if you have a pull request). Afterwards, remove the 'To Update !' label and add the 'Status: Updated' label.

1. Progress: "What is the current status of your project? What have you completed and what is left to do?"
2. Blockers: "Difficulties or errors encountered."
3. Availability: "How much time will you have this week to work on this issue?"
4. ETA: "When do you expect this issue to be completed?"
5. Pictures: "Add any pictures of the visual changes made to the site so far."

If you need help, be sure to either: 1) place your issue in the developer meeting discussion column and ask for help at your next meeting, 2) put a "Status: Help Wanted" label on your issue and pull request, or 3) put up a request for assistance on the #hfla-site channel.

_{You are receiving this comment because your last comment was before Monday, February 14, 2022 at 11:18 PM PST.}

[SAUMILDHANKAR]

Progress: Didn't work on this issue last week. Made progress on the issue to fix the bug in 2 weeks inactive label. Blockers: No blockers right now. Availability: 3 hrs ETA: 2/25/22

[SAUMILDHANKAR]

Progress: Created a new function that adds Google form data to the summary sheet, added an on form submit trigger to the function. Almost complete, need to test on the final version. Blockers: Final testing to be done once #2901 is merged. Availability: Not aplicable ETA: To be determined.

[SAUMILDHANKAR]

Moving this issue to the ice box column and adding dependency since demo to the team won't work until #2901 is fixed.

Code so far that adds data to the Summary1 sheet

/**
/************************************************** TRIGGER("On Form Submit") 4 SECTION ********************************************************************/
/*
  For each new wins submission, add data to the "Summary1" Sheet.
*/

function insertLatestFormSubmitIntoSummarySheet(formSubmitEvent){

	const rawFormDataObject = formSubmitEvent.namedValues;
	const formDataInsertedAt = formSubmitEvent.range; 
  
	const reviewSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Review1");
	const responsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");
	const summarySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary1");

	let processedFormDataObject = new Map();
	processedFormDataObject.set("Email", rawFormDataObject["Email Address"])
	processedFormDataObject.set("Name", rawFormDataObject["Full name"])
	processedFormDataObject.set("LinkedIn", rawFormDataObject["Linkedin URL (optional)"])
	processedFormDataObject.set("LinkedIn Picture Allowed ?", rawFormDataObject["Could we use your Linkedin profile picture next to your story?"])
	processedFormDataObject.set("GitHub", rawFormDataObject["Github URL (optional)"])
	processedFormDataObject.set("GitHub Picture Allowed ?", rawFormDataObject["Could we use your Github profile picture next to your story?"])
	processedFormDataObject.set("Teams", rawFormDataObject["Select the team(s) you're on"])
	processedFormDataObject.set("Roles", rawFormDataObject["Select your role(s) on the team"])
	processedFormDataObject.set("Specific Roles", rawFormDataObject["What is/was your specific role? (optional)"])
	processedFormDataObject.set("Celebrations", rawFormDataObject["What do you want to celebrate (select all that apply)?"])
	processedFormDataObject.set("Overview", rawFormDataObject["Give us a brief overview"])

	const memberSince = rawFormDataObject["When did you join Hack for LA? (optional)"]

	// Prepare display string from proccssed form data object
	let displayString = "";
	for(const [key,value] of processedFormDataObject ){
        displayString += `${key} : ${value} \n\n`;
	}

	// Create and insert a new empty row before row 2
	summarySheet.insertRowBefore(4);
	
	// Insert member since value on column 3
	summarySheet.getRange(4,3).setValue(memberSince);
	
	// Insert display overview value on column 4
	summarySheet.getRange(4,4).setValue( displayString );
	
	// Set the column 'A'  for new row created in the 'Summary1' sheet by form submission to be mapped to the new row created in the 'review1' sheet
	summarySheet.getRange(4,1).setFormula("=Review1!A3");
	
	// Set the column 'B'  for new row created in the 'Summary1' sheet by form submission to be mapped to the new row created in the 'review1' sheet
	summarySheet.getRange(4,2).setFormula("=Review1!B3");
	
	// Set the column 'E'  for new row created in the 'Summary1' sheet by form submission to be mapped to the new row created in the 'review1' sheet
	summarySheet.getRange(4,5).setFormula("=Review1!E3");
	
	// Set formula to count the role of each row
	summarySheet.getRange(4,9).setFormula("=COUNTIF(E4,$I$1)");
	
}  

[github-actions[bot]]

Hi @roslynwythe, thank you for taking up this issue! Hfla appreciates you :)

Do let fellow developers know about your:- i. Availability: (When are you available to work on the issue/answer questions other programmers might have about your issue?) ii. ETA: (When do you expect this issue to be completed?)

You're awesome!

P.S. - You may not take up another issue until this issue gets merged (or closed). Thanks again :)

[roslynwythe]

Availability: 12/18 10 am - 8 pm; 12/19 9 am - noon; 12/20 5 pm - 10 pm; 10/21 10 am - 5 pm ETA: 12/30

[roslynwythe]

Hi @arpitapandya @macho-catt - The issue description, states that I need to make a copy of the Wins form response sheet and Secret files in my own drive. I have the copy of the Wins form response sheet but I need permission/advice how to copy the Secret files, I'm not certain but I don't think I have access to the Hfla website admin folder; I couldn't find the link to that folder. I was surprised that the issue says that I should get access to the Secret files, since the Wins Form Admin Guide says that only merge-team and admin accounts should have access, and for this issue I don't need to see the super-sensitive key files directly; all I need is to be able to call the gh-library from my account to test my code changes to the Response sheet script, but presumably that is not possible without the Secret file and container.

I believe that for testing I need a copy of the Google form; is it possible to share a copy with me or to provide a link that would enable me to make a copy. The link I have for the Google form does not provide any menu options.
Thank you.

[roslynwythe]

Progress:

1. The first 4 top-level action items have been completed.
2. I am currently testing the new code function and sheet formulas in my copy of wins-response. I also populated the new Homepage Summary sheet with all the recent wins submissions to bring it up to date and in sync with the Review Sheet.
3. I do have some minor questions which I will pose tonight at the dev office hours.
4. I expect to be ready to demonstrate the code next week.

[roslynwythe]

Progress: Code and spreadsheet formulas have been written and tested, but I wanted to confirm my approach to managing the common fields between the Homepage Summary and Review sheets. In particular, is it acceptable to populate these columns on the Homepage Summary sheet using formulas that map to the corresponding columns on the Review sheet:

• display
• homepage
• member since
• display summary

[roslynwythe]

unassigning because dependency https://github.com/hackforla/website/issues/2901 not completed.

[arpitapandya]

moving this back to the ice-box as #2901 isn't ready.

[jdingeman]

Marking as ready for prioritization

[roslynwythe]

• @ExperimentsInHonesty unless this issue is time sensitive, it would be preferable to make it dependent on #3641 and #4134. Please advise. By the way, I had virtually completed work on this issue before #2901 was reopened.

[ExperimentsInHonesty]

@roslynwythe I moved this into the new issue review column because the dependency is resolved. Please add a ready for prioritization label if its ready.

[ExperimentsInHonesty]

@roslynwythe I moved this into the new issue review column because the dependency is resolved. Please add a ready for prioritization label if its ready.

[roslynwythe]

@ExperimentsInHonesty questions:

• I assume that the following columns are formulas pulling values from the corresponding columns on the Review sheet: Display, Homepage, Manual Reviewed Role
• Regarding the columns circled in red below: I assume we are estimating the number of views based on the number of wins entries in the json file. That calculation will change each time there is a change to the Homepage column. Please let's discuss

Homepage Summary sheet - screenshot

• can we get started on this now: #4190

[roslynwythe]

@ExperimentsInHonesty Looking at "Homepage Summary Prototype" I see that the "Display" and "Homepage" columns are formulas pointing to the Review sheet, but it seems likely that a user might try to change the value of those fields from the "Homepage Summary Prototype" sheet. How can we prevent that?

[github-actions[bot]]

Hi @roslynwythe, thank you for taking up this issue! Hfla appreciates you :)

Do let fellow developers know about your:- i. Availability: (When are you available to work on the issue/answer questions other programmers might have about your issue?) ii. ETA: (When do you expect this issue to be completed?)

You're awesome!

P.S. - You may not take up another issue until this issue gets merged (or closed). Thanks again :)

[ExperimentsInHonesty]

@roslynwythe We could hide it, but that might be a problem later when we need to see it and forget its there.

I just tried changing the color of the font, in case that helps. But also, I don't see that the Display column is a formula, only the homepage column. Not sure if that was supposed to connect and never got setup properly. Should we update it?

@ExperimentsInHonesty Looking at "Homepage Summary Prototype" I see that the "Display" and "Homepage" columns are formulas pointing to the Review sheet, but it seems likely that a user might try to change the value of those fields from the "Homepage Summary Prototype" sheet. How can we prevent that?

[roslynwythe]

@roslynwythe We could hide it, but that might be a problem later when we need to see it and forget its there.

I just tried changing the color of the font, in case that helps. But also, I don't see that the Display column is a formula, only the homepage column. Not sure if that was supposed to connect and never got setup properly. Should we update it?

I updated the Display column to be a formula. The Homepage and Member Since columns were already formulas. I also made Manual Reviewed Role a formula as well since that seems necessary, and I'm thinking Display Review should be a formula as well, although that column could be copied from the Review sheet. Do you agree?

[ExperimentsInHonesty]

@roslynwythe

• I updated the Review tab to have a drop-down for the role.
• I don't see where you added a formula for the Manual Reviewed Role on the Homepage summary prototype tab.
• I added to the Review spreadsheet a column for test and populated it with TRUE/FALSE values
• Before we start making more formulas for the content on the homepage summary prototype, let's discuss how we plan to update the columns (what automation and how it will work). The reason I ask this is because, if the columns A-E are all being pulled from Review tab, and then we add new info to Review tab, and then that updates the content in Homepage summary prototype tab, it could potentially disconnect the data in the below columns from the data in A-E.
  • Date Live on homepage start
  • Date Live on homepage end
  • No of times appeared on website

[roslynwythe]

@roslynwythe

• Before we start making more formulas for the content on the homepage summary prototype, let's discuss how we plan to update the columns (what automation and how it will work). The reason I ask this is because, if the columns A-E are all being pulled from Review tab, and then we add new info to Review tab, and then that updates the content in Homepage summary prototype tab, it could potentially disconnect the data in the below columns from the data in A-E.

  • Date Live on homepage start
  • Date Live on homepage end
  • No of times appeared on website

@ExperimentsInHonesty perhaps we should integrate those homepage summary columns with the Review sheet.