Update Google Apps Script to generate wins data feed only on Change

[roslynwythe]

Dependency

• [x] #3641
• [x] #4134
• [x] #4035

Overview

As developers we need to modify the Apps Script in the Wins-form (Responses) spreadsheet so that a json feed is pushed to GitHub only after the data has changed, in order to eliminate the need to examine daily commits for changes.

Action Items

Setup

• [ ] Follow sections 1-3 in the Wins Apps Script Development Process[^3]. 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[^1] as VIEWER
  • share the Wins-form (Response)[^2] 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.

Suggested Solutions to Implement

• In gh-requests, modify getSHA(filename) so that it returns not just the SHA but also the json contents. To reflect this change, change the name of the function to getWins(filename). Back in main(), compare the returned JSON to the data just compiled from the spreadsheet. If differences are detected, proceed to call updateWinsFile.
• If a different approach will be taken, explain the approach in a comment and bring to the attention of a dev lead.

Testing

• [ ] Test in debug mode: confirm that when main() runs when there have been no changes to Wins data, the script will not create any commits/PRs in the test GitHub repository
• [ ] In the Review sheet, find a row in which the "Display" column has the value "True" and change it to "False". Run main() in debug mode and confirm that a new commit and a new Pull Request "Update wins-data.json via Google Apps Script" is created in your test repository. Run main() again and confirm that no additional commits have been noted in the Pull Request. Merge the Pull Request into your test branch then view the wins webpage in the test branch and confirm that the corresponding wins entry is not displayed.

After successful testing

• [ ] Update the Wins Admin Guide and the wiki "Automations for Wins page using Google Apps Script" with a description of the new logic for pushing the wins data feed to GitHub.
• [ ] Add/commit Code.js then proceed to create a Pull Request as usual. Contact the dev lead to arrange review

dev lead:

• [ ] Remove developer's access from each shared document and as well as any folder level access
  • [ ] Wins Form Admin Guide[^1]
  • [ ] Wins-form (Response)[^2] sheet
  • [ ] Wins-form[^3]
  • [ ] gh-requests doc
• [ ] See instructions in Wins Apps Script Development Process[^4] under the heading "For Reviewers (Merge Team)"

Resources/Instructions

• _wins-data.json file
• Wins webpage [^1]:Wins Form Admin Guide [^2]:Wins-form (Response) Sheet [^3]:Wins Apps Script Development Process

[roslynwythe]

@jdingeman I have described two alternative solutions to this issue. They can be found in the section "Solution to Implement" under Action Items. Please advise which approach you favor.

[ExperimentsInHonesty]

all the dependencies have been cleared, this issue needs to be reviewed and if it's good, add a ready for prioritization label.

[github-actions[bot]]

Hi @iancooperman, 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 :)

[iancooperman]

ETA for this entire issue: 7/3/2024. I think I could complete the setup steps by this coming Sunday, 6/9/2024. Availability this week: Tues-Fri 5pm-9pm, Sat 10am-9pm, Sun 10am-4pm.

[ExperimentsInHonesty]

@iancooperman I have moved this issue to the In Progress column for you. Please remember to do that in future when you assign an issue. Thanks!

[iancooperman]

Output of step 5.3 on gh-requests in Wins Google Apps Script Development Process:

git diff Code.js
warning: in the working copy of 'google-apps-scripts/gh-requests/Code.js', LF will be replaced by CRLF the next time Git touches it
diff --git a/google-apps-scripts/gh-requests/Code.js b/google-apps-scripts/gh-requests/Code.js
index 2c14f52a..edb0ea73 100644
--- a/google-apps-scripts/gh-requests/Code.js
+++ b/google-apps-scripts/gh-requests/Code.js
@@ -119,7 +119,8 @@ function setToken_() {
   }

   const doc = DocumentApp.openById(id);
-  documentProperties.setProperty('TOKEN', doc.getBody().getText())
+  documentProperties.setProperty('TOKEN', doc.getBody().getText());
+  console.log(documentProperties.getProperty(`TOKEN`))
 }

 // Uses base64 to decode an input

The only significant difference between the version of Code.js cloned from Google and the one from GitHub is the addition of one line of logging.

[iancooperman]

No such output for wins-form-respones.

git diff Code.js

[iancooperman]

Hi!. Still working on this! Life just got in the way. Hope to have this done by the end of next week at the latest.

[github-actions[bot]]

@iancooperman

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 (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 Tuesday, August 6, 2024 at 12:04 AM PST.}

[iancooperman]

Successfully set up test environment and finally resolved authentication issues on my end. Just need to make the appropriate changes in both files. ETA: 8/17

[iancooperman]

Will need to implement a deeper comparison of the wins-form-responses and _wins.json than just comparing their lengths. ETA: 8/25

[HackforLABot]

@iancooperman

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 (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 Tuesday, August 27, 2024 at 12:04 AM PST.}

[roslynwythe]

Hi @iancooperman please leave an update regarding the comparison you mentioned above. Also, are you still having authentication errors when posting _wins_data.json to your test repository? I am asking because I would like to know if there is something missing in the instructions. Thanks Ian.

[iancooperman]

Hey @roslynwythe. I'm sorry for not being more active. I've been dreadfully sick the past few days.

After trying various methods to go through the spreadsheet data and the _wins-data.json file for direct comparisons, I decided to simply convert the formatted spreadsheet data and the contents of the existing file into two strings and compare those. I know it might sound a bit unconventional, but it has passed every test I've given it so far.

I'd like to test it a bit more before making a PR for this. And I also haven't run into the authentication error again. Go figure.

[iancooperman]

I am currently working on writing new material for the Wins Admin Guide and the wiki "Automations for Wins page using Google Apps Script". PR will come soon after that is finished.

[iancooperman]

Proposed updated entry for "Automations for Wins page using Google Apps Script":

Automations for Wins page using Google Apps Script

• As soon as a user submits the share your wins form, the On form submit triggers two functions:
  • The first function: Which grabs the data from the form and reformats it, and adds it to the same spreadsheet the form response goes to, but on another more readable tab (review)
    • Parses users' response using JSON.parse()
    • Adds a timestamp to when the form was submitted
    • Formats the response to be used in our spreadsheet
    • Calls the Google App Script
    • Posts response to the review tab in the spreadsheet.
  • The second function:
    • Creates an issue to be added to our project board.
• The user's response will require an admin review to be considered for the Wins page.
• Next, a time-based trigger which triggers daily, in the same Google Apps Script, creates a Pull Request which if merged adds the new row (in the spreadsheet with a True value in the Display column) to the Wins page on the website. Note that if no new rows or other changes have been made to the spreadsheet, no pull request is made.