Google Apps Script: Add unique IDs as object keys for each wins entry

[macho-catt]

Dependency

• CLASP for Google Apps Scripts development evaluation / decision  #3722
• Add latest version of Google Apps Scripts to GitHub #3641
• Create wiki page "Hfla Google Apps Script Development/Review Process" #4134
• Google Apps Script: Migrate Wins Data - Stop generation of AF file #2147

Overview

To expedite the process of locating a specific wins entry, each wins entry (which is also a javascript object) should have a unique ID as a key.

Action Items

• Follow sections 1-3 in the Wins Apps Script Development Process¹. Note that in this issue you will be modifying both Apps Scripts wins-form-responses and gh-requests so setup will be required in both /google-apps-script/wins-form-responses and /google-apps-script/gh-requests
• 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:
  • do not modify folder level access
  • share the Wins Form Admin Guide² as VIEWER
  • share the Wins-form (Response)³ sheet as VIEWER
  • share the gh-requests doc as VIEWER
• Continue following instructions in 3c and d. As described in 3d, you will have to make modifications to certain documents in the test folder.
  • It will not be necessary to restore any triggers, because you can execute main() in debug mode.
  • Since you will be running main() for testing, you must follow instructions in Testing WINS in a test repository
• 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.
  • Modify wins so that on the creation of the javascript object for each entry, a unique ID is generated as well
  • This ID can be the row number of the wins entry
• Ensure that even with the addition of the unique IDs as keys, the current wins page should functionally look and work the same way in your own repo.
• Demo your changes to the website leads so they can approve and add it to the production script

Resources/Instructions

Google Apps Script
_wins-data file
wins page JS

Footnotes

1. Wins Apps Script Development Process ↩

2. Wins Form Admin Guide ↩

3. Wins-form (Response) Sheet ↩

[lopezpedres]

Progress:

Done

• Asked and got permission to get access to the drive HfLA website Admin
• Made a copy, in my own drive, of the Google Sheets file with Its corresponding App Script from the file Wins-form (Responses)
• Made a copy, in my own drive, of the Google Doc file with Its corresponding App Script from the file gh-request
  
• Added line 44 in the Google Sheets App Script

   39      const keyValueData = filteredRows.map((row,i) => {
   40      const valueObject = new Object();
   41      row.forEach((value, j) => {
   42       valueObject[columnHeaders[j]] = value;
   43        })
+  44     valueObject['uniqueId']= 2+ i
   45
   46       return valueObject;
   47    })

• In my copy, I added some more lines of code to get the output in a Google Doc file where I could see that the previous added line actually works:

Doing

• After checking that the unique iD for each object was set correctly and corresponded to the row number in the Wins-form (Responses) file, I wanted to try to use the GitHub API to update the _wins-data.json file in my repository. Just to check that the output in the GitHub repository would be the same as the one in the previous image.

  Problems

• For some reason that I still can't figure it, whenever I try to make a PUT request, which is part of the process of updating the _wins-data.json file (line 186 of the gh-request App Script ), using my own token and repository, I get a 404 response.

Blockers: No blockers
Availability: 10 hrs
ETA: December 30th

[ExperimentsInHonesty]

open a new issue, and self assign to Please add the instruction on when and how to encode a token to the contributing.md file

[SAUMILDHANKAR]

@lopezpedres Thanks for making a PR. Please add the final code changes that you have made here. Also, the mobile and desktop screenshots of the wins page that you see in your local env after merging PR as well as the unique ID that is generated in wins-data.json file. It will be helpful to get all this documented with this issue. I will add the changes to the main gs code app script and close the PR after your demo on Sunday. Thank you.

[lopezpedres]

@SAUMILDHANKAR Thank you! I'll attach the two screenshots here:

Mobile Screenshot

Desktop Screenshot

Example of an UniqueID generated

The key for all the objects is "UniqueID" and its value is the Timestamp when the form was submitted as well as the email of the creator of the submit. Example:

{ "UniqueID" : "2020-11-30T06:17:25.226Z : johnsritchey@yahoo.com"}

[SAUMILDHANKAR]

// Create an array of objects (key-value pair) based on the column headers and rows of values so the data does not need to be formatted later in GitHub
  const keyValueData = filteredRows.map(row => {
    const valueObject = new Object();
    row.forEach((value, i) => {
      valueObject[columnHeaders[i]] = value;
    })
    const date = valueObject["Timestamp"]
    const formatDate = Utilities.formatDate(date, 'Etc/GMT', 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\'')
    valueObject["UniqueID"]=`${formatDate} : ${valueObject["Email Address"]}`
    return valueObject;
  })

lines 7, 8, 9 to be added in the main code.

[SAUMILDHANKAR]

• This issue is almost complete. The final code to be added is in this comment. We couldn't test if the wins page is still functionally the same because of the open issue Google Apps Script: Fix data errors in spreadsheet used for wins page #2901. Ideally, the developer working on this issue should be able to:
• add the final code in their copy of the Google Apps Script.
• use a dummy GitHub account to create a PR in their own repo.
• merge the PR in their own repo and then build the wins page using their own repo and make sure all the functionalities related to the wins page are still the same.

So, last few action items are still pending and therefore, adding dependency and moving this to ice box. Again, thank you Miguel for all your hard work on this. Great work!

[roslynwythe]

I assigned @daras-cu, a new member of the merge team, because she is interested in becoming familiar with WINS in order to write WINS issues and support other devs.

[daras-cu]

Update

I have added code to the main() function in Code.gs to assign an ID to each wins submission in _wins-data.json based on its spreadsheet row number. I came across the following while testing:

• In the Responses sheet linked to the Wins form, new wins submissions were sometimes added as a new row at the top of the sheet instead of the bottom.
• Adding a row to the top of the sheet would change the row numbers of previous submissions and therefore change their ID when generating the _wins-data.json file.
• Sorting the sheet by the Timestamp column resolved this issue by ensuring new entries were at the bottom of the sheet.
• To ensure IDs remain consistent, I suggest adding a step to main() to sort the Responses sheet before pulling any of the data from it.

Follow-up question: Do rows ever get deleted from the Responses sheet? This would also change the row numbers of other submissions, so I may need to use a different method of generating IDs.

[dcotelessa]

Link to updated insructions for wins_data issue:
#2385

[ExperimentsInHonesty]

@daras-cu We do not delete rows from the responses sheet

[daras-cu]

Update:
Progress: Finished working on issue, currently working on demo for leads.
Blockers: None
Availability: Weekdays after 6pm Pacific, weekends
ETA: 11/30 EOD

[HackforLABot]

@daras-cu

Please add update using the below template (even if you have a pull request). Afterwards, remove the '2 weeks inactive' 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 (optional): "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 Questions/In Review column of the Project Board 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. Please note that including your questions in the issue comments- along with screenshots, if applicable- will help us to help you. Here and here are examples of well-formed questions.

_{You are receiving this comment because your last comment was before Monday, December 30, 2024 at 11:04 PM PST.}

[daras-cu]

Update:
I made a video demo (YouTube link) showing the following:

• Adding a new Wins submission through the Google Form
• Auto generation of a GitHub issue to review submission
• Running the Code.gs script to auto generate a PR adding the new submission to _wins-data.js
• Merging PR and testing display of the Wins page locally