Meeting Data Audit: hfla website

[Rabia2219]

Overview

We need to audit all calendar events for a given project to match the Zoom Setup and Zoom Audit Spreadsheet so that we can optimize the availability of the Zoom rooms and have the most current meeting information.

Prerequisite

If you are not the PM on the project this issue is for

• access to the 1Password vault for HfLA-email-all
• know your GitHub username and password so that you can use it in the hfla-email-all@hackforla.org Chrome profile

If you are the PM on the project this issue is for

• access to the 1Password vault for the project
• access to the project's shared drive through the project's email Chrome profile
  • add the project's email as a Content Manager if the drive is not already shared with the email
• know your GitHub username and password so that you can use it in the project email's Chrome profile

Set up

Set up Access Through Proper Profile

• Non PMs
  • Log into the hfla-email-all@hackforla.org email and open it in a new chrome profile, so that you can access the account's calendar
  • open this issue in the hfla-email-all profile browser and follow steps to Access GitHub
• PMs
  • Log into the project's email and open it in a new chrome profile, so that you can access the account's calendar
  • open this issue in the project's profile browser and follow steps to Access GitHub

Access GitHub

• Log into your GitHub account. If you are already logged in you will see either your icon. If you are not, you will see a sign-in button top right in the issue that is open now in the new profile browser.
• Close any other browsers you have open that are not in the new profile browser, so that you don't end up opening links in the wrong browser (this will be particularly important when you are accessing drives)

Add Org Level Audits folder to Starred

If you do not see the project or CoP's drive in the list of shared drives, see Prerequisite above

• access the Org Level Audits folder in the project or CoP's shared drive, it will be preceded by the project or CoP initials
  • a star next to the folder name indicates that it has already been starred and you don't need to do the next step
• right click on the folder and select Add to starred

Notes

There are automation formulas used throughout the meeting data and audit reconciliation sheet to autofill cells and generate messages regarding meetings with nonstandard setup. These formulas are indicated in a light grey color.

• During the Admin Prep you will be asked to confirm that there are formulas in the correct cells and columns, which will easily be recognizable by the light grey.
• Please do not edit any cells that are indicated to have formulas

Troubleshooting

Please make sure you read through all instructions carefully. There are some errors that are expected and should be automatically resolved as you continue with the tasks outlined in each action item. Please review Errors and How To Resolve Them to get an idea of what to expect. If, while carrying out any of the tasks, there is still ambiguity or there are errors that are not listed with a solution on the page mentioned above, please follow these instructions to contact the Admin team.

Action Items

There are some action items below that are linked to a wiki page with instructions (they are displayed in blue). Open the link in a new tab, copy the instructions from the wiki page into a comment below and complete the tasks in the comment.

Prep

During the first task you will be asked to add a link under Resources/Instructions, and that section is at the bottom of this issue (go view it now).

• [x] Create Meeting Data Audit and Reconciliation Spreadsheet (open in new tab)
• [x] After the first Prep item has been checked off,
  • [x] remove the prep label
  • [x] add the admin prep label
  • [x] move issue to Prioritized Backlog on the project board
  • [x] unassign yourself
  • [x] remove the Org Level Audits folder from Starred
    • [x] access https://drive.google.com/drive/starred (open in new tab)
    • [x] look for the Org Level Audits folder that belongs to the project or CoP, it will be preceded by the initials
    • [x] right click on the folder and select Remove from starred

Admin Prep

• [x] Set up Meeting Data Audit and Reconciliation Spreadsheet for the Audit
• [x] After the first 2 Admin Prep items have been checked off and their labels removed,
  • [x] remove the admin prep label
  • [x] add the audit label

Audit

• [x] Perform Calendar Event Audit
• [x] Perform Zoom Accounts Audit
• [x] Perform hackforla.org/VRMS Audit
• [x] After the first 3 Audit items have been checked off and their labels removed,
  • [x] remove the audit label
  • [x] add the status: needs post audit messaging label
  • [ ] move issue to Prioritized Backlog on the project board
  • [ ] unassign yourself

Recommendations and next steps

• [x] Create and send message to PMs via Slack
• [ ] Perform the post-audit next steps to standardize all meeting data
• [ ] Review team feedback and respond

Resources/Instructions

1.0 Draft: HfLA website: Meeting Data Audit and Reconciliation

[ExperimentsInHonesty]

Create Meeting Data Audit and Reconciliation Spreadsheet

• [x] go to Template for Meeting Audit
• [x] select USE TEMPLATE button
• [x] add the newly created document's link to resource 3.0
• [x] rename file by replacing [PROJECT NAME] with the project or CoP name you are performing the audit for
• [x] move file to the Org Level Audits folder in the project's or CoP's drive
  • [x] choose move icon or file menu item, Move in the spreadsheet
  • [x] use back arrow (top left)
  • [x] choose Shared Drives
  • [x] open project drive
  • [x] open folder inside project or CoP drive (with same name as project or CoP drive)
  • [x] open the Org Level Audits folder
  • [x] choose Move here
  • [x] choose Move
• [x] check the box for Create Meeting Data Audit and Reconciliation Spreadsheet in the Action items of this issue
• [x] Remove status: needs audit spreadsheet label
• [x] close the tabs you just opened (wiki and spreadsheet)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Create Slack Audit document

• [x] go to Template for Slack Audit
• [x] select USE TEMPLATE button
• [x] add the newly created document's link to resource 4.0
• [x] open resource 3.0
  • [x] add link to the Slack Audit document on the Details sheet, B5
• [x] rename file by replacing [PROJECT NAME] with the name of the project or CoP you are performing the audit for
• [x] select ctrl+H (Google's Find & Replace feature) and replace [Replace PROJECT NAME] with the project's or CoP's name
• [x] replace [Replace PROJECT INITIALS] with project's or CoP's initials
• [x] move file to the Org Level Audits folder in the project's or CoP's drive
  • [x] choose move icon or file menu item, Move in the spreadsheet
  • [x] use back arrow (top left)
  • [x] choose Shared Drives
  • [x] open project drive
  • [x] open folder inside project or CoP drive (with same name as project or CoP drive)
  • [x] open the Org Level Audits folder
  • [x] choose Move here
  • [x] choose Move
• [x] check the box for Create Slack Audit document in the Action items of this issue
• [x] remove status: needs Slack audit document label
• [x] close the tabs you just opened (wiki, document and spreadsheet)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Create Recommendations & Next Steps document

• [x] go to Template for Post Audit Recommendations
• [x] Select USE TEMPLATE button
  • [x] add the newly created document's link to resource 4.0
• [x] rename file by replacing [Replace PROJECT NAME] with the name of the project or CoP you are performing the audit for
• [x] select ctrl+H (Google's Find & Replace feature) and replace [Replace PROJECT NAME] with the project's or CoP's name
• [x] move file to the Org Level Audits folder in the project's or CoP's drive
  • [x] choose move icon or file menu item, Move in the document
  • [x] use back arrow (top left)
  • [x] choose Shared Drives
  • [x] open project drive
  • [x] open folder inside project or CoP drive (with same name as project of CoP drive)
  • [x] open the Org Level Audits folder
  • [x] choose Move here
  • [x] choose Move
• [x] check the box for Create Recommendations & Next Steps document in the Action items of this issue
• [x] remove `status: needs post audit recommendations document' label
• [x] close the tabs you just opened (wiki and document)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Import project's data from Zoom Setup spreadsheet

• [x] open resource 1.0 in a new window
• [x] view side by side with the issue window
• [ ] create a filter for your project
  • [x] go to the Schedule tab in the Zoom Setup (resource 1.0)
  • [x] select the green down arrow next to Project Name in column A
  • [x] under Filter by values, select Clear
  • [x] search for your project name in the search bar
  • [x] select your project name so that a checkmark appears next to it
  • [x] select OK
• [x] copy sheet into resource 2.0
  • [x] right click on the Schedule sheet tab at the bottom
  • [x] select Copy to
  • [x] then select Existing spreadsheet
  • [x] in the window that opens up, type the name of resource 3.0 and press enter
  • [x] click on the file in the results
  • [x] select Insert
  • [x] then select Open spreadsheet in the pop up message
• [x] remove irrelevant (other projects') data
  • [x] rename the Copy of Schedule sheet to Overall Audit
  • [x] drag it to 2nd position (next to the Details tab)
  • [x] select the green funnel next to Project Name in column A
  • [x] under Filter by values, select Select All
  • [x] unselect your project name so that the checkmark next to it disappears
  • [x] select OK
  • [x] select the data starting from A2 all the way to the end
  • [x] right click and select Delete selected rows
  • [x] select the green funnel next to Project Name in column A
  • [x] under Filter by values, select Select All
  • [x] select OK
  • [x] undo the filter in Zoom Setup (resource 1.0)
  • [x] select OK in the Sheet copied successfully pop up message
  • [x] select the green funnel next to Project Name in column A
  • [x] under Filter by values, select Select All
  • [x] select OK
• [x] remove
  • [x] status: needs Zoom Setup data label
• [x] check the box for Import project's data from Zoom Setup spreadsheet in the Action items of this issue
• [x] IF you are NOT continuing to the next step in Admin Prep, close the tabs you just opened (wiki, spreadsheets)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

• [x] open resource 2.0 in a new window
• [x] view side by side with the issue window

  Setup the Overall Audit Sheet

• [x] access the Overall Audit sheet and do the following:
  • [ ] change the header color to #38761d (the second to last green in the Fill color option)
  • [ ] change the header font color to white
  • [x] change the header name in Col C to Name of Meeting in Zoom Setup Spreadsheet
  • [x] hide columns D, N, O, V and U
  • [x] insert a column at the end and name the header Auto Notes
  • [x] in the column named Auto Notes, copy and paste the following formula

    IF(AND(C2='hackforla.org-VRMS'!A3,C2='Calendar Event Audit'!A2,C2='Zoom Accounts Audit'!B2,C2='Slack Audit'!B3),'hackforla.org-VRMS'!K3&Char(10)&'Calendar Event Audit'!S2&Char(10)&'Zoom Accounts Audit'!K2&Char(10)&'Zoom Accounts Audit'!K17&Char(10)&'Slack Audit'!Y3&Char(10)&'Slack Audit'!Y7&Char(10)&'Slack Audit'!Y11&Char(10)&'Slack Audit'!Y15)

  • [x] insert 3 more columns at the end (do not name them) NOTE: there will be more formulas to add once all the other sheets have been setup

    Setup the Calendar Event Audit Sheet

    NOTE: This sheet is divided into 3 sections indicated by the colored rows

• [ ] copy the sheet URL starting from https till the / before edit
• [ ] there are 3 query functions on this sheet (2 in column A and 1 in column R), do the following for each:
  • [ ] replace [Replace URL] with the URL you just copied
  • [ ] add an = sign before both the function
  • [ ] press Enter at the end of the formula to save
  • [ ] check that the values for column R changed to YES
• [ ] in cell S2, add an = sign the function then press Enter at the end of the formula to save
• [ ] copy the formula down till all the cells with data are covered
• [ ] adjust the number of rows in each section as needed once populated with the data

  Setup the hackforla.org-VRMS Sheet

  NOTE: This sheet is divided into 3 sections indicated by the colored rows

• [ ] next, access the hackforla.org-VRMS sheet and do the following:
  • [ ] copy the sheet URL starting from https till the / before edit
  • [ ] there are 2 query functions on this sheet (cell C2 and J2), do the following for each:
    • [ ] replace [Replace URL] with the URL you just copied
    • [ ] add an = sign before the function
    • [ ] press Enter at the end of the formula to save
    • [ ] check that the values for column J changed to YES
    • [ ] adjust the number of rows in each section as needed once populated with the data NOTE: the formula in column K is already setup

      Setup the Zoom Accounts Audit Sheet

      NOTE: This sheet is divided into 3 sections indicated by the colored rows

• [ ] remove irrelevant (other projects') data
  • [x] select the green funnel next to Team in column G
  • [x] under Filter by values, select Select All
  • [x] unselect your project name so that the checkmark next to it disappears
  • [x] select OK
  • [ ] select the data starting from A2 till column H and the last row above row 53 (till the green border of the filter)
  • [x] right click and select Delete selected rows
  • [x] select the green funnel next to Team in column G
  • [x] under Filter by values, select Select All (only your project's name should appear)
  • [x] select OK
  • [x] undo the sheet filter by selecting the green funnel in the tool bar
• [ ] pull data from Zoom Setup Spreadsheet
  • [ ] for the query function in column B for the section tilted Data Pulled from Zoom Setup Spreadsheet, do the following:
    • [ ] copy the sheet URL starting from https till the / before edit
    • [ ] replace [Replace URL] with the URL you just copied
    • [ ] add an = sign before the query function
    • [ ] press Enter at the end of the formula to save
• [x] adjust meeting names to match
  • [x] in the main section, adjust the rows under column A (Name of Meeting in Zoom Account) to match the sequence of meetings under column B (Name of Meeting in Zoom Setup Spreadsheet) under the Data Pulled from Zoom Setup Spreadsheet section
  • [x] if there is a meeting that is not listed in column A, add a missing row in it's place
• [ ] there are 2 more query functions on this sheet (cell B1 and I1), do the following for each:
  • [ ] replace [Replace URL] with the URL you just copied
  • [ ] add an = sign before both the function
  • [ ] press Enter at the end of the formula to save
  • [ ] check that the values for column I changed to YES
• [ ] adjust the number of rows in each section as needed once populated with the data

  Setup the Slack Audit sheet

  NOTE: This sheet is divided into 2 sections indicated by the colored rows

• [ ] there is 1 query function on this sheet (cell B2), do the following:
  • [ ] replace [Replace URL] with the URL you just copied
  • [ ] add an = sign before both the function
  • [ ] press Enter at the end of the formula to save NOTE: We are working with a hypothetical number of channels for now that will be updated to reflect the actual number during the Slack audit process
• [ ] adjust the number of rows in each channel to match the number of meetings

  Add Remaining Formulas to Overall Audit Sheet

• [ ] access the Overall Audit tab
• [x] in column F,
  • [x] add an = sign
  • [ ] access the Zoom Accounts Audit tab
  • [ ] select the first cell in column I under the Data Pulled from Zoom Setup Spreadsheet section
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column G,
  • [ ] add an = sign
  • [ ] access the Zoom Accounts Audit tab
  • [ ] select cell A2
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [x] in column H,
  • [ ] add an = sign
  • [ ] access the Calendar Events Audit tab
  • [ ] select the first cell in column J under the Data Pulled from Zoom Setup Spreadsheet section
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column I,
  • [ ] add an = sign
  • [ ] access the Calendar Events Audit tab
  • [ ] select cell B2
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column J,
  • [ ] add an = sign
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column K,
  • [ ] add an = sign
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column L,
  • [ ] add an = sign
  • [ ] access the hackforla.org-VRMS tab
  • [ ] select cell D3
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] in column M,
  • [ ] add an = sign
  • [ ] access the hackforla.org-VRMS tab
  • [ ] select cell E3
  • [ ] press Enter to save
  • [ ] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down
• [ ] check the box for Setup Automation Formulas for the Audit in the Action items of this issue
• [ ] close the tabs you just opened (wiki, spreadsheet)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Reconcile the Slack Audit Document with the Slack Audit Sheet

• [x] open resource 2.0 in a new tab
  • [x] access the Slack Audit tab
• [x] open resource 3.0 in a new window
• [x] view side by side

  Customize the Slack Audit tab

  NOTE: There are 2 sections in this sheet indicated by the colored rows

• [ ] if there is only 1 Slack channel mentioned in resource 3.0, delete the rest of the rows in the 1st section and proceed to the Perform Reconciliation step below
• [ ] otherwise, repeat for each channel mentioned in resource 3.0
  • [ ] under the last row for channelname1, insert as many rows as there are meetings
  • [ ] copy the block of prefilled meeting data in columns B,C and D
  • [ ] paste it in column B of the first empty row
  • [ ] copy the channel name in column A for each meeting
  • [ ] select ctrl+H (Google's Find & Replace feature) and replace [Replace channlename1] with the 1st channel name in resource 3.0 and so on for channelname2, channelname3

[Rabia2219]

Overview

We need to audit all calendar events for a given project to match the Zoom Setup and Zoom Audit Spreadsheet so that we can optimize the availability of the Zoom rooms and have the most current meeting information.

Prerequisite if you are not the PM on the project this issue is for

• access to the 1Password vault for Google Drive Migration
• know your GitHub username and password so that you can use it in the (Google Drive Migration) hfla-email-all@hackforla.org Chrome profile

Action Items

Access GitHub through (Google Drive Migration) hfla-email-all@hackforla.org (only for people who are not the PM on the project this issue is for).

• [x] Log into the hfla-email-all@hackforla.org email and open it in a new chrome profile, so that you can access the account's calendar
• [x] open this issue in the hfla-email-all browser
• [x] Log into your GitHub account. If you are already logged in you will see either your icon. If you are not, you will see a sign-in button top right in the issue that is open now in the hfla-emaill-all browser.
• [x] Close any other browsers you have open that are not hfla-email, so that you don't end up opening links in the wrong browser (this will be particularly important when you are accessing drives).

Prep

For each of the following items, there are instructions in the wiki. Copy the instruction for next item to be checked off (must do them in order), to a comment below, and then complete the tasks in the comment.

During these tasks you will be asked to add link to resources, and that section is at the bottom of this issue (go view it now).

• [x] Create Meeting Data Audit and Reconciliation Spreadsheet
• [x] Create Slack Audit document
• [x] Create Recommendations & Next Steps document
• [x] After the first 3 Prep items have been checked off and their labels removed,
  • [x] remove the prep label
  • [x] add the admin prep label
  • [x] move issue to Prioritized Backlog on the project board
  • [x] unassign yourself

Admin Prep

• [x] Import project's data from Zoom Setup spreadsheet
• [x] Setup Automation Formulas for the Audit
• [ ] After the first 2 Admin Prep items have been checked off and their labels removed,
  • [ ] remove the admin prep label
  • [ ] add the audit label

Audit

NOTE: There are autogenerated notes in the spreadsheet you will be using so please do not edit any Auto Notes columns

• [ ] Perform Calendar Event Audit
• [ ] Perform hackforla.org/VRMS Audit
• [ ] Perform Zoom Accounts Audit
• [ ] Gather & document data from channels
• [ ] After the first 4 Audit items have been checked off and their labels removed,
  • [ ] remove the audit label
  • [ ] add the reconciliation label
  • [ ] move issue to Prioritized Backlog on the project board
  • [ ] unassign yourself

Reconciliation

• [ ] Reconcile the Slack Audit document with the Slack Audit Sheet

Recommendations and next steps

• [ ] Add recommendations
• [ ] Review team feedback and respond

Resources/Instructions

1.0 Zoom Setup Spreadsheet 2.0 HfLA website: Meeting Data Audit and Reconciliation Spreadsheet 3.0 HfLA website: Slack Audit Document 4.0 HfLA website: Post Audit Recommendations

[ExperimentsInHonesty]

Create Meeting Data Audit and Reconciliation Spreadsheet

• [x] go to Template for Meeting Audit (open in new tab)
• [x] select USE TEMPLATE button
• [x] add the newly created document's link to resource 3.0
• [x] rename file by replacing [Replace PROJECT NAME] with the project or CoP name you are performing the audit for

  Move File to the Org Level Audits folder in the Project's or CoP's Drive

  Start with Option 1 and if the Org Level Audits folder is not in Starred use Option 2

• [x] Option 1
  • choose move icon or file menu item, Move in the spreadsheet
  • use back arrow (top left)
  • choose Starred
  • open the project's or CoP's Org Level Audits folder (indicated by the project or CoP initials)
  • choose Move here
  • choose Move
• [ ] Option 2
  • choose move icon or file menu item, Move in the spreadsheet
  • use back arrow (top left)
  • choose Shared Drives
  • open project drive
  • open folder inside project or CoP drive (with same name as project or CoP drive)
  • open the Org Level Audits folder
  • choose Move here
  • choose Move

You should be on the Details tab of the spreadsheet after moving the file, but in case you are not, please access the Details tab of the spreadsheet

Paste the spreadsheet URL in the Details tab

• [x] locate and copy the sheet URL starting from https till the / before edit
• [x] paste it into cell B7 in the Details tab of the spreadsheet

  Add project name and initials

• [x] in cell B8, add the project name*
• [x] in cell B9, add the project initials*

  Allow access to external spreadsheet

• [x] in cell B10, if there is an error #REF!, hover over it and select the green Allow access button

  Paste Slack audit URL in the Details tab

• [x] right click on resource 2.0 in the main issue under Resources/Instructions
• [x] copy the document URL (copy link address)
• [x] paste it into cell B5 in the Details tab of the spreadsheet

  Paste Slack channel(s) in the Details tab

• [x] open resource 2.0
• [x] view side by side with the Details tab of the spreadsheet
• [x] from resource 2.0, copy the first channel name; including the #
  • Use the links under OUTLINE on the left to access the section/page for each channel name for ease of copying
• [x] paste it into cell B12 in the Details tab of the spreadsheet
• [x] select Enter to save
• [x] repeat for all the channel names listed in resource 2.0, using commas w/o spaces to separate each channel name
• [x] check the Table of Contents on page 1 of resource 2.0 to make sure all the channel names have been pasted into cell B12

  Close Out this Task

• [x] check the box for Create Meeting Data Audit and Reconciliation Spreadsheet in the Action items of this issue
• [x] Remove status: needs audit spreadsheet label
• [x] close the tabs you just opened (wiki, spreadsheet and document)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Import project's data from Zoom Setup spreadsheet

• [x] open resource 1.0 in a new window
• [x] view side by side with the issue window

  Copy the name of resource 3.0 (your project or CoP's audit spreadsheet)

• [x] go to the Resources/Instructions section of the main issue
• [x] copy the name of resource 3.0 (leave out the resource number)

  Import sheet into resource 3.0

  • [x] access the Zoom Setup spreadsheet and right click on the Schedule tab of (tabs are at the bottom)
  • [x] select Copy to
  • [x] then select Existing spreadsheet
  • [x] in the window that opens up, paste the name of resource 3.0 (already copied) in the search bar and press enter
  • [x] select the file in the results that show up
  • [x] select Insert
  • [x] select OK in the Sheet copied successfully pop up message

    Remove irrelevant (other projects') data

  • [x] open resource 3.0
  • [x] rename the Copy of Schedule sheet to Overall Audit (use the arrows next to the tab names and scroll right to find the sheet)
  • [x] drag it to 2nd position (after the Details tab)
  • [x] select the white down arrow next to Project Name in column A
  • [x] under Filter by values, search for your project or CoP name
  • [x] unselect your project or CoP name so that the checkmark next to it disappears
  • [x] select OK
  • [x] select the data starting from the first cell in column A all the way to the last cell in column U
  • [x] right click and select Delete selected rows
  • [x] select the green funnel next to Project Name in column A
  • [x] under Filter by values (only your project or CoP name should appear), select Select All
  • [x] select OK

    Close out this task

• [x] remove status: needs Zoom Setup data label
• [x] check the box for Import project's data from Zoom Setup spreadsheet in the Action items of this issue
• [x] close the tabs you just opened (wiki, spreadsheets)
• [ ] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Set up Automation Formulas

Open Resource(s)

• [x] open resource 3.0 in a new window
• [x] view side by side with the issue window

You will be accessing different sheets/tabs in resource 3.0 during this process to set them up for the audit.

Set up the Overall Audit Sheet

Access the Overall Audit tab

Formatting

• [x] change the column name in Col C to Name of Meeting in Zoom Setup Spreadsheet
• [x] change the column name in Col E to 'In Zoom Setup Spreadsheet'
• [x] remove the sheet filter by selecting the green funnel in the tool bar

  Remove Validation in Column J SKIP

• [ ] select column J
• [ ] right click and select Data Validation
• [ ] select the rule for column J in the panel that opens up
• [ ] select Remove Rule
• [ ] select the X next to the Data validation rules at the top of the panel

  Insert Columns

• [x] insert 6 columns to the right of column M
  • press CTRL and select cells H1 through M1
  • select Insert in the menu
  • select click and select Columns
  • Insert 6 columns right
• [x] name column N Auto Notes
• [x] name column O Changes Required

  Add Formula

• [x] copy and paste the following formula in cell N2, and copy it till the last row (select cell N2, hover over the small blue square or circle, grab the + symbol and drag it till the last row)

  =TEXTJOIN(CHAR(10),TRUE,IF(AND(C2='hackforla.org-VRMS'!C4,C2='Calendar Event Audit'!A4,C2='Zoom Accounts Audit'!B4,C2='Slack Audit'!B3),'hackforla.org-VRMS'!K4&Char(10)&'Calendar Event Audit'!U4&Char(10)&'Zoom Accounts Audit'!L4&Char(10)&'Slack Audit'!AA3&Char(10),""))

• [x] copy and paste the following formula in cell O2, and copy it till the last row (select cell O2, hover over the small blue square or circle, grab the + symbol and drag it till the last row)

  =TEXTJOIN(CHAR(10),TRUE,IF(AND(C2='hackforla.org-VRMS'!C4,C2='Calendar Event Audit'!A4,C2='Zoom Accounts Audit'!B4,C2='Slack Audit'!B3),'hackforla.org-VRMS'!L4&Char(10)&'Calendar Event Audit'!V4&Char(10)&'Zoom Accounts Audit'!M4&Char(10)&'Slack Audit'!AB3&Char(10),""))

  You are done with this tab

Set up the Next Steps Sheet (This sheet is divided into 2 sections)

Access the Next Steps tab

In Section 1

• [x] select cell A3, select enter twice (the first meeting name should appear)
• [x] copy the formula till the last row with a meeting name
• [x] select cell B3, select enter twice (the error should disappear)
• [x] copy the formula till the last row with a meeting name
• [x] select cell C3, select enter twice (the error should disappear)
• [x] copy the formula till the last row with a meeting name
• [x] delete all rows without any meeting names except for one

You are done with this tab.

Set up the Calendar Event Audit Sheet (This sheet is divided into 3 sections)

Access the Calendar Event Audit tab

In Section 1

• [x] check that column A has been auto filled
  • if there is a #REF! error in cell A3, hover over it and select the green Allow access button
• [x] delete all rows without any meeting names except for one
• [x] check that the values for column T changed to YES
• [x] check that the formula in column U is copied till the last row with a meeting name in this section
• [x] check that the formula in column U is copied till the last row with a meeting name in this section
• [x] check that column W has been auto filled

  In Section 3

• [x] check that columns A,D,E,F,H and J have been auto filled
• [x] delete all rows without any meeting names except for one (SHIFT+CRTL+down arrow will select all empty rows)

  In cell B1

• [x] add the cell number of the first auto filled cell in column A of Section 3

You are done with this tab.

Set up the Zoom Accounts Audit Sheet (This sheet is divided into 3 sections)

Access the Zoom Accounts Audit tab

In Section 1

Delete all the data that is not related to your project from this sheet

• [x] select the down arrow next to Team in column H
• [x] under Filter by values, search for your project or CoP name or initials or short name*
• [x] unselect your project name so that the checkmark next to it disappears
• [x] select OK
• [x] select the data starting from the first cell in column A till the green border of the filter, the second last cell in column H (do not cross over into Section 2 or column I)
• [x] right click and select Delete selected rows
• [x] select the funnel next to Team in column G
• [x] under Filter by values, select Select All (only your project's name should appear)
• [x] select OK
• [x] undo the sheet filter by selecting the green funnel in the tool bar

In Section 2

There is a formula set in this section to filter out meetings that are in any Zoom account but not on the Zoom Setup spreadsheet. As you move data around and add formulas, it is expected that the values in this section will keep changing.

In Section 3

• [x] check that column B through H have been auto filled
• [x] delete all empty rows except the last one

  In Section 1 again

  In this step, the meeting names in column A of Section 1 will be moved around to match the sequence of meeting in column B of Section 3. It is very likely that the names will not match, so try to match the Day of the week and Meeting Time Start (Pacific Time), etc. of meetings that sound similar.

• [x] move the rows up or down to place a meeting in the right order (to match with column B of Section 3)
• [x] add a row for a meeting that is not listed in column A but appears in column B of Section 3

Next, formulas will be added. As you go through these steps, some cells may turn red. That is expected.

• [x] add an = sign before the function in cell B2
• [x] press Enter at the end of the formula to save
• [x] check that the values for column I changed to YES
• [x] delete all rows without data except for one

If any !REF errors pop up during these steps, ignore them. They should be resolved after the last action item for this tab is complete.

• [x] paste the following formula in column J and copy it till the last row with data

  
  =IF(A4<>"","YES","NO")

- [x] paste the following formula in column K and copy it till the last row with data

=IFS(G4="", "", AND(G4<>"",OFFSET(INDIRECT($B$1),ROW()-ROW($G$4),5)=G4), "YES", TRUE, "NO")

- [x] paste the following formula in column L and copy it till the last row with data

=IF(B4="","",TEXTJOIN(Char(10),TRUE,IF(K4="NO",Categories!$E$9,""),IF(J4="NO",Categories!$E$13,""),IF(AND(A4<>"",A4<>N4),Categories!$E$22,""),IFS(E4="","",AND(E4<>"",TRIM(OFFSET(INDIRECT($B$1),ROW()-ROW($E$4),3))<>TRIM(E4)),Categories!$E$27,TRUE,""),IFS(F4="","",AND(F4<>"",TRIM(OFFSET(INDIRECT($B$1),ROW()-ROW($F$4),4))<>TRIM(F4)),Categories!$E$24,TRUE,""),IFS(C4="","",AND(C4<>"",OFFSET(INDIRECT($B$1),ROW()-ROW($C$4),1)<>C4),Categories!$E$26,TRUE,"")))

- [x] paste the following formula in column M

=IF(B4="","",TEXTJOIN(Char(10),TRUE,IF(K4="NO",Categories!$H$9,""),IF(J4="NO",Categories!$H$13,""),IF(AND(A4<>"",A4<>N4),Categories!$H$22&A4&" to "&N4,""),IFS(E4="","",AND(E4<>"",TRIM(OFFSET(INDIRECT($B$1),ROW()-ROW($E$4),3))<>TRIM(E4)),Categories!$H$27,TRUE,""),IFS(F4="","",AND(F4<>"",TRIM(OFFSET(INDIRECT($B$1),ROW()-ROW($F$4),4))<>TRIM(F4)),Categories!$H$24,TRUE,""),IFS(C4="","",AND(C4<>"",OFFSET(INDIRECT($B$1),ROW()-ROW($C$4),1)<>C4),Categories!$H$26,TRUE,"")))

#### In cell B1
- [x] add the cell number of the first auto filled cell in column B of Section 3

_You are done with this tab._

### Set up the `hackforla.org-VRMS` Sheet (This sheet is divided into 3 sections)
Access the `hackforla.org-VRMS` tab
#### In Section 2
- [x] check that column C, D, E and F have been auto filled
- [x] delete all rows without data except for one
- [x] check that the values for column J changed to `YES`
- [ ] delete all rows without any data except for one
- [x] check that the formula in columns K and L is copied till the last filled row in this section
#### In Section 3
- [x] check that the formula in column K and L is copied till the last row in this section

_You are done with this tab._

### Set up the `Slack Audit` sheet
Access the `Slack Audit` tab
#### In Section 1
- [x] check that columns B through F have been auto filled
- [ ] add channel names to column A

_For this step, we will be pasting data from the `Details` sheet into column A of the `Slack Audit` sheet repeatedly. The number of repetitions will be equal to the number of Slack channels minus one as the first channel name is already pasted in. Channel names can be found under cell B14 (`Slack Channel Names`)._

Please see the before and after screenshots below so you have an understanding of what needs to be done.
<details>
  <summary>Before</summary>
  <img src="https://user-images.githubusercontent.com/75643389/227306195-4d4eaf5a-0feb-4eac-b01e-f6c9fa776b3d.png">
</details> 
<details>
  <summary>After</summary>
 <img src="https://user-images.githubusercontent.com/75643389/227311711-eb3854fe-3889-4ee5-a000-3f17495ec3a1.png">
</details>

- [x] in the `Slack Audit` sheet, copy the channel name in cell A3 till the last cell with corresponding data in column B (each meeting name in column B should have the same channel name in column A)
- [x] repeat the following for each remaining channel name
   - [x] copy the next channel name from the `Details` tab and paste it into the next empty cell in column A of the `Slack Audit` sheet. As a reminder Channel names can be found under cell B14 (`Slack Channel Names`).
   - [x] copy the same channel name into the cells below to match the number of meetings (e.g. if there are 9 meetings, the channel name should be repeated 9 times)
- [x] paste the following formula in the first empty cell in column B and copy it till the last row with corresponding data in column A

=IFERROR(FILTER(B3:F3,OFFSET(A3,ROW()-ROW(A3),0)<>"",OFFSET(A3,ROW()-ROW(A3),0)<>A3),"")

- [x] after all channel names have been added, delete all rows without data except for one 
   - select the row where you want to start, select SHIFT+CRTL+down arrow
   - with SHIFT+CRTL still selected, find and select the last row
   - release SHIFT
   - right click, select `Delete rows 00 - 00

_You are done with this tab._

### Close out this task
_You are done with all the steps in this task. You will now close it out in the main body of the issue._

- [x] check the box for `Setup Automation Formulas for the Audit` in the `Admin Prep` action items of this issue
- [x] remove `status: needs automation formulas` label
- [x] close the tabs you just opened (wiki, spreadsheet)
- [ ] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform Calendar Event Audit

Open Resources

• [ ] if you are the pm on the project, open the project's email account and go to the calendar
• [x] Else, if you are the doing this work via the hfla-email-all, open the calendar for hfla-email-all
  • [x] access the project's calendar by finding the email/name in the left panel, under My calendars, and checking the box(es) (make sure no other boxes are checked)
  • [x] open resource 3.0 in a new window
  • [x] view side by side

    Access the Calendar Event Audit tab (There are 3 sections in this sheet)

    In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the team's calendar. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match. Important Note: Do not audit meetings with a Does not repeat value for Meeting Recurs.

    In Section 1

    You will match meetings in the Zoom Setup spreadsheet with the team's calendar and add relevant data.

  • [ ] for each meeting in column A, fill out columns B through S (skip columns I and K) using the information on the team's calendar for the matching meeting
  • if any meeting in column A is not on the team's calendar, fill only column B by selecting "NO"
  • to fill out column J Zoom Account, use the following:
    • if the Zoom account is not mentioned, select Unknown
    • if you know that it is a team member's personal account, select Personal
    • if the link starts with: https://us02web.zoom.us/j/445145****?, select Bonnie's Zoom

      In Section 2

      You will determine if there are meetings on the team's calendar that are not in the Zoom Setup spreadsheet and add relevant data.

  • [ ] for meetings that appear on the team's calendar but are not in Section 3: Data Pulled from Zoom Setup Spreadsheet, fill out columns C through S (skip columns I and K)

Close out this task

You are done with all the steps in this task. You will now close it out in the main body of the issue.

• [x] check the box for Perform Calendar Event Audit in the Audit action items of this issue
• [x] remove status: needs calendar event audit label
• [x] close the tabs you just opened (wiki, spreadsheet, calendar)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform Zoom Accounts Audit

Open resource

• [x] open resource 3.0 in a new window

• [x] view side by side

  Access the Zoom Accounts Audit tab (There are 3 sections in this sheet)

  The audit has been performed for this sheet using the automation formulas. You will confirm that the generated results are accurate.

  In Section 1

• [x] Confirm that there are no #REF! or #N/A errors. (contact Admin via Slack in the #admin channel if there are any)

  In Section 2

  In this section, meetings that do not match with the Zoom Setup spreadsheet have been copied using formulas.

• [x] Confirm that there are no #REF! errors. (contact Admin via Slack in the #admin channel if there are any)

• [x] Confirm that meetings with data in column A and no data in column B of Section 1 have been copied into this section, with values in column A, C, D, E, F, G and I.

  Close out this task

  You are done with all the steps in this task. You will now close it out in the main body of the issue.

• [x] check the box for Perform Zoom Accounts Audit in the Audit action items of this issue

• [x] remove status: needs Zoom accounts audit label

• [x] close the tabs you just opened (wiki, spreadsheet)

• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform Slack Audit

Open resources

• [x] open resource 2.0
• [x] access Hack for LA's Slack workspace through https://hackforla.slack.com/ (use the method you regularly use)
• [x] view side by side

  Gather meeting data from Slack

  In this step, you will search for a channel name in Slack, copy the relevant data in from the reminders, pins, bookmarks, description and topic of that channel and paste it in resource 2.0, under the appropriate headings for each channel name. * If you are not a member of the channel, you can join temporarily to collect the data and then leave * The pins and bookmarks appear on the bar under the channel name > * If there is no pin icon, then there are no pins and therefore, no data to collect > * If there are no bookmarks, the text + Add a bookmark will appear and therefore, no data to collect > > * If there are 3 dots after a bookmark name, there are more bookmarks, so click on the dots for the remaining bookmark names > > * If there are nested bookmarks, there will be a down arrow next to the main bookmark's name * The topic and description appear in a pop up window, which is accessed by selecting the down arrow next to the channel name > * If the text Add a topic appears under the Topic heading in the pop up window, there is no data to collect > * If the text Add a description appears under theDescription` heading in the pop up window, there is no data to collect

  Repeat for every channel name in resource 2.0

• [ ] in resource 2.0, locate and copy the channel name
• [ ] in Hack for LA's Slack workspace, search for the channel name by pasting it in the search bar
• collect the following data from the channel
  • [x] Reminders
    • [ ] in Slack, go to the channel
    • [ ] type /remind list in a comment and hit enter
    • [ ] copy the resulting text
    • [ ] in resource 2.0, paste it under the Reminders for that channel
    • [ ] write none if there's no data
  • [x] Pins
    • [ ] in Slack, click on the pin icon
    • [ ] copy the messages
    • [ ] in resource 2.0, paste under the Pins for that channel
    • [ ] write none if there's no data
  • [x] Bookmarks
    • [ ] in Slack, locate the bookmark(s)
    • [ ] in resource 2.0, write the name of the bookmark as it appears and the resource type under the Bookmarks for that channel (e.g. Developer Meeting Tuesday - Zoom Link)
    • for nested bookmarks, write the names as indented bullets
    • [ ] in Slack, right click on the bookmark, copy link and link it to the resource name you just wrote
  • [x] Topic
    • [ ] in Slack, select the down arrow next to the Slack channel name
    • [ ] look for the section named Topic in the window that pops up
    • [ ] copy the text
    • [ ] in resource 2.0, paste under the Topic for that channel
    • [ ] for emojis
    • [ ] in Slack, right click on the emoji, copy image
    • [ ] in resource 2.0, paste it with corresponding topic
    • [ ] write none if there's no data
  • [x] Description
    • [ ] in Slack, select the down arrow next to the Slack channel name
    • [ ] look for the section named Description in the window that pops up
    • [ ] copy the text
    • [ ] in resource 2.0, paste under the Description for that channel
    • [ ] for emojis
    • [ ] in Slack right click on the emoji, copy image
    • [ ] in resource 2.0, paste it with corresponding description
    • [ ] write none if there's no data

      Format resource 2.0

• [ ] refresh the Table of Contents (TOC) on the first page
• [x] add the appropriate emojis to each category
  • [x] go to the Legend on Page 1 of the document
  • [x] copy the 👁️‍🗨️ (eye emoji) and paste it in front of every category heading (Reminders, Pins, Bookmarks, Topics, Description) for each channel that has any information (e.g. there is information for Reminders in a channel: Reminders 👁️‍🗨️)
  • [x] copy the 🚫 (no emoji) and paste it in front of every category heading (Reminders, Pins, Bookmarks, Topics, Description) for each channel that doesn't have any information (e.g. there is no information for Pins in a channel: Pins 🚫)
  • [x] refresh the Table of Contents (TOC) on the first page The TOC will look something like this when you are done
• [x] update the Last Updated date in the footer
• [x] remove Draft from the file name

Close out this task

You are done with all the steps in this task. You will now close it out in the main body of the issue.

• [x] check the box for Perform Slack Audit in the Audit action items of this issue
• [x] remove the status: needs Slack audit label
• [x] reminder: if you are not the PM on the project leave all the Slack channels you joined after the audit is complete
  • Instructions on how to leave a Slack channel
• [x] close the tabs you just opened (wiki, document, Slack)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform hackforla.org/VRMS Audit

Project specific steps (scroll down for CoP specific steps)

Open Resource(s)

• [x] open resource 3.0
• [x] access https://www.hackforla.org/projects
• [x] locate your project card and access the project's page
• [x] view side by side
• [ ] copy the URL for your project's page
• [ ] on the Details sheet in resource 3.0, replace the text that says https://www.hackforla.org/projects/[Replace PROJECT-NAME], in B6 with the URL

  Access the hackforla.org-VRMS tab (There are 3 sections in this sheet)

  In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the project's page. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match. Important Note: Do not audit meetings with a Does not repeat value for Meeting Recurs.

In Section 1

• [ ] check the No events on website (check if applicable, which completes VRMS audit) box if there's no meeting data on the project's page

  In Section 2

  You will match meetings in the Zoom Setup spreadsheet with the project's page and add relevant data.

• [x] for each meeting in column C, fill out columns G through I using the information on the project's page for the matching meeting
  • if any meeting in column C is not on the project's page, fill only column G by selecting "NO"

    In the Section 3

    You will determine if there are meetings on the project's page that are not in the Zoom Setup spreadsheet and add relevant data.

• [x] fill out columns D through I (skip column G) for meetings that appear on the project's page but are not in column C of Section 1

CoP specific steps

Open Resource

• [ ] open resource 3.0
• [ ] open the URL https://www.hackforla.org/communities-of-practice
• [ ] find the CoP
• [ ] view side by side
• [ ] on the Details sheet in resource 3.0, replace the text that says https://www.hackforla.org/projects/[Replace PROJECT-NAME], in B6 with https://www.hackforla.org/communities-of-practice

Access the hackforla.org-VRMS tab (There are 3 sections in this sheet)

In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the CoP's page. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match. Important Note: Do not audit meetings with a Does not repeat value for Meeting Recurs.

In Section 1

• [ ] check the No events on website (check if applicable, which completes VRMS audit) box if there's no meeting data on the CoP's page

  In Section 2

  You will match meetings in the Zoom Setup spreadsheet with the CoP's page and add relevant data.

• [ ] for each meeting in column C, fill out columns G through I using the information on the CoP's page for the matching meeting
  • if any meeting in column C is not on the CoP's page, fill only column G by selecting "NO"

    In Section 3

    You will determine if there are meetings on the CoP's page that are not in the Zoom Setup spreadsheet and add relevant data.

• [ ] fill out columns D through I (skip column G) for meetings that appear on the CoP's page but are not in column C of Section 1

Close out this task

You are done with all the steps in this task. You will now close it out in the main body of the issue.

• [x] check the box for Perform hackforla.org/VRMS Audit in the Audit action items of this issue
• [x] remove the status: needs hackforla.org/VRMS audit label
• [x] close the tabs you just opened (wiki, spreadsheet, website)
• [x] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Reconcile the Slack Audit Document with the Slack Audit Sheet

Open Resources

• [x] open resource 2.0 in a new tab
• [x] open resource 3.0 in a new window
• [x] view side by side

  Access the Slack Audit tab (There are 2 sections in this sheet)

  _You will

  In Section 1

• [ ] repeat for each channel
  • [ ] repeat for each meeting in channel
    • [ ] search for any kind of reference to the meeting in resource 2.0
    • [ ] update resource 3.0 (columns G to Z) accordingly
      • [ ] select Unknown if the meeting day and/or recurrence are not mentioned

        In Section 2

• repeat for each meeting in any channel in resource 2.0 that is not mentioned in column B of Section 1
  • [ ] add the channel name
  • [ ] leave columns C through F blank and add available data about the meeting (columns G to Z)

Close out this task

You are done with all the steps in this task. You will now close it out in the main body of the issue.

• [ ] remove Draft from the file name in resource 2.0
• [ ] check the box for Reconcile the Slack Audit document with the Slack Audit Sheet in the Reconciliation action items of this issue
• [ ] remove
  • [ ] the status: needs Slack reconciliation label
  • [ ] the reconciliation label
• [ ] add the recommendations label
• [ ] close the tabs you just opened (wiki, spreadsheet, document)
• [ ] hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

@Rabia2219 Notes about the slack audit

The one meeting that is not mentioned properly is the the Thursday 7pm Office Hours. So Ava's Pin in the #hfla-site channel where its mentioned

Is not really a meeting that is not mentioned but rather a meeting whose name and function have been changed. So I don't know how to note that in the spreadsheet. Ill hold off on closing the above comment until we talk about it.

[audreychurch]

Updated

Add Slack channels

Open resources

• [x] open resource 2.0
• [x] access Hack for LA's Slack workspace through https://hackforla.slack.com/ (use the method you regularly use)
• [x] view side by side

  Get channel names

• [x] in Hack for LA's Slack workspace, search for the project or CoP channels in the search bar
  • use full name AND initials
  • there may be multiple channels, e.g. access-the-data, access-the-data-lead, so scroll down in the search results to make sure you get all the channels
• [x] compare the channel list, for both search by name and initials, with the list in resource 2.0 and if they don't match
  • [x] copy and paste a block of headings for an existing channel to a new page for each new channel found
  • [x] replace the channel name for each block with the new channel
  • [x] refresh the Table of Contents (TOC) on the first page and double check to make sure all the Slack channel names were added
  • [x] update the Last Updated date in the footer

[audreychurch]

I found five additional Slack channels.

[ExperimentsInHonesty]

Create Meeting Data Audit and Reconciliation Spreadsheet

Create meeting data audit spreadsheet

• [x] Use the template to create a new document for your audit
  • open the Template for Meeting Audit in a new tab
  • select USE TEMPLATE button
  • rename file by replacing [Replace PROJECT NAME] with the project or CoP name you are performing the audit for
  • add the newly created document's name and link to resource 1.0
• [x] Move File to the Org Level Audits folder in the project's or CoP's drive Start with Option 1 and if the Org Level Audits folder is not in Starred use Option 2
• Option 1
  • choose move icon or file menu item, Move in the spreadsheet
  • use back arrow (top left)
  • choose Starred
  • open the project's or CoP's Org Level Audits folder (indicated by the project or CoP initials)
  • choose Move here
  • choose Move
• Option 2
  • choose move icon or file menu item, Move in the spreadsheet
  • use back arrow (top left)
  • choose Shared Drives
  • open project drive
  • open folder inside project or CoP drive (with same name as project or CoP drive)
  • open the Org Level Audits folder
  • choose Move here
  • choose Move

    Close Out this Task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Create Meeting Data Audit and Reconciliation Spreadsheet in the Action items of this issue
  • remove status: needs audit spreadsheet label
  • close the tabs you just opened (wiki, spreadsheet and document)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Set up Setup Meeting Data Audit and Reconciliation Spreadsheet for the Audit

• [x] Open Resource(s)
  • open resource 1.0 in a new window
  • setup side by side view with the issue window

You will be accessing different sheets/tabs in resource 2.0 during this process to set them up for the audit.

Set up the Details tab

Access the Details tab and complete the following

• [x] add project or CoP name and initials in the Details tab
  • in cell B9, add the project or CoP name from the drop down
  • in cell B10, add the project or CoP name from the drop down
  • in cell B11, add the project or CoP name from the drop down
  • in cell B12, add the project or CoP initials from the drop down
• [x] Allow access to external spreadsheet
  • in cell B12, check that there is at least one Slack handle (name preceded by @)
    • if there is a #REF! error, hover over it and select the green Allow access button
    • if cell B12 is still empty, please follow these instructions to contact the Admin team
    • [x] add the bug label and do not proceed further

You are done with this tab.

Set up the Overall Audit Sheet

Access the Overall Audit tab and complete the following

• [x] delete any empty row
  • this sheet should now be auto populated with meeting data
    • if there is meeting data, delete all extra (empty rows) except for one
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
    • [ ] add the bug label and do not proceed further
• [x] columns M and N are light grey and have data
  • there should be some autogenerated notes in these 2 columns (these values will change as the audit is performed in a later step)

You are done with this tab

Set up the Next Steps Sheet

Access the Next Steps tab and complete the following

• [x] Set up Section 1.1: Meeting Audit Results
  • this sheet should now be auto populated with meeting data
    • if there is meeting data, delete all extra (empty rows) except for one
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
    • [ ] add the bug label and do not proceed further
  • there should be some autogenerated notes in columns B and C for all rows with meeting data (these will get updated once the audit is complete)
  • column E should be light grey for all rows with meeting data (have a formula)
• [x] Set up Section 1.2: Meetings with 'Does not Repeat' status
  • if there are any meetings with the Does not Repeat status they will appear in this section with data in columns A, B, C and D
    • the first cell in this section should be light grey (has a formula)
    • all rows in columns C, D and E, except the last one, should be light grey (have a formula)
• [x] Set up Section 1.3: Meetings not in the Zoom Setup Spreadsheet but mentioned in
  • the first cell in Section 1.3.1 should be light grey (has a formula)
  • the first cell in Section 1.3.2 should be light grey (has a formula)
  • the first cell in Section 1.3.3 should be light grey (has a formula)
  • all cells in column E, except the last, should be light grey (have a formula)

You are done with this tab.

Set up the Calendar Event Audit Sheet

Access the Calendar Event Audit tab and complete the following

• [x] Set up Section 1: Add meeting data from team calendar
  • column A should be auto filled
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
      • [ ] add the bug label and do not proceed further
  • all rows in columns B, O, P, T and U, except the last one, should be light grey
  • the values for column S changed to YES for each meeting name in column A
  • column V should be auto filled
  • delete all rows without any meeting names except for one
• [x] Set up Section 2: Add meetings that are on the team calendar but not in Section 3
  • all rows in columns O, P, S, T and U, except the last one, should be light grey
• [x] Set up Section 3: Data Pulled From Zoom Setup Spreadsheet
  • columns A, E, F, G, I and J should be auto filled
  • delete all rows without any meeting names except for one (SHIFT+CRTL+down arrow will select all empty rows)
• [x] In cell C1, replace the current cell number with the cell number of the first cell with a meeting name in column A of Section 3

You are done with this tab.

Set up the Zoom Accounts Audit Sheet

Access the Zoom Accounts Audit tab and complete the following

• [x] Set up Section 1: Meetings in the Zoom Setup Spreadsheet
  • columns B through G should be auto filled
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
      • [ ] add the bug label and do not proceed further
  • column A has the value 'NO' for meeting names in column B (these values will change as the audit is performed in a later step)
  • the values for column N changed to YES for each meeting name in column B
  • columns O and P are light grey and have data (these values will change as the audit is performed in a later step)
  • column Q should be auto filled
  • delete all rows without any data except for one
• [x] Set up Section 3: Meeting data from Zoom accounts
  • columns B through G should be auto filled
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
      • [ ] add the bug label and do not proceed further
  • column A has the value 'YES' for meeting names in column B (these values will change as the audit is performed in a later step)
  • columns O and P are light grey and have data (these values will change as the audit is performed in a later step)
  • delete all rows without any data except for one

You are done with this tab.

Set up the hackforla.org-VRMS Sheet

Access the hackforla.org-VRMS tab and complete the following

• [x] Set up Section 2: Meetings in the Zoom Setup Spreadsheet
  • columns D through G should be auto filled
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
      • [ ] add the bug label and do not proceed further
  • column C has the value 'NO' for meeting names in column D (these values will change as the audit is performed in a later step)
  • the values for column L changed to YES for each meeting name in column D
  • columns M and N are light grey and have data (these values will change as the audit is performed in a later step)
  • delete all rows without any data except for one
• [x] Set up Section 4
  • select the menu item Get Data and then select Get Recurring Events
  • columns D through G should be auto filled
    • if there is an error, please review Errors and How To Resolve Them in a new tab
    • in case the error is unresolved and you have had to reach out to the Admin team
      • [ ] add the bug label and do not proceed further
  • column C has the value 'YES' for meeting names in column D
  • check that the formula in column M and N is copied till the last row with a meeting name in column D in this section (cells with formulas will turn light grey)
• [x] Set up Section 1
  • columns A and B should auto hide if the Get Recurring Events found and pulled meetings into Section 4

You are done with this tab.

Close out this task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Setup Automation Formulas for the Audit in the Admin Prep action items of this issue
  • remove status: needs needs spreadsheet set up label
  • close the tabs you just opened (wiki, spreadsheet)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform Zoom Accounts Audit

• [x] Open resource
  • open resource 1.0 in a new window
  • set up side by side view with the issue window

    Compare meetings in the Zoom Setup spreadsheet against the Zoom accounts

    In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the Zoom accounts. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match.

Access the Zoom Accounts Audit tab and complete the following

• [x] in Section 2: Add Meeting Data from Section 3, find matching meetings in Section 3 and copy the data into columns H through M of Section 2 The values in column A, O and P will change as you perform this audit step
  • start with the first meeting in column B of Section 1 and find it's match in Section 3
  • copy the Name of Meeting, Zoom #, Day of the week, Meeting Time Start (Pacific Time), Meeting Time End (Pacific Time), Meeting Recurs and paste (Crtl+Shift+V) the values into columns H through M in Section 2
  • if the meeting name is not in Section 3, leave the corresponding columns (H through M) empty in Section 2 for that meeting
• [x] in Section 3, indicate meetings that are in a Zoom account but not in the Zoom Setup spreadsheet
  • if there are any meetings in this section with no match in Section 1 (you did not copy the data into columns H through K of Section 2), select No in column N for that meeting

Close out this task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Perform Zoom Accounts Audit in the Audit action items of this issue
  • remove status: needs Zoom accounts audit label
  • close the tabs you just opened (wiki, spreadsheet)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform Calendar Event Audit

• [x] Open Resources
  • if you are the PM on the project, open the project's email account and go to the calendar
  • if you are the doing this work via the hfla-email-all, open the calendar for hfla-email-all
    • access the project's calendar by finding the email/name in the left panel, under My calendars, and checking the box(es) (make sure no other boxes are checked)
  • open resource 1.0 in a new window
  • set up side by side view with issue window, team's calendar and resource 1.0

    Compare meetings in the Zoom Setup spreadsheet against the team's calendar

    In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the team's calendar. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match. Perform the audit starting from the 1st of the next month. Important Note: Do not audit meetings with a Does not repeat value for Meeting Recurs.

Access the Calendar Event Audit tab and complete the following

• [x] in Section 1: Add meeting data from team calendar, add information about the meetings using the information on the team's calendar
  • on the team's calendar, select the meeting you want to audit, then select the pencil icon to access the details about the meeting
  • fill out columns C through R (columns O and P will auto fill if YES is selected for column N)
    • if any meeting in column A is not on the team's calendar, fill only column C by selecting "NO"
    • to fill out column J Zoom Account, use the following:
    • if the Zoom account is not mentioned, select Unknown
    • if you know that it is a team member's personal account, select Personal
    • if the link starts with: https://us02web.zoom.us/j/445145****?, select Bonnie's Zoom
  • once all the information for the meeting has been added, select the X next to the meeting name on the team's calendar to exit the details window
• [x] in Section 2: Add meetings that are on the team calendar but not in Section 3, add information about meetings on the team's calendar that are not listed in Section 1
  • on the team's calendar, select the meeting you want to edit, then select the pencil icon to access the details about the meeting
  • fill out columns D through R, using the same criteria as above for column J
  • once all the information for the meeting has been added, select the X next to the meeting name on the team's calendar to exit the details window

Close out this task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Perform Calendar Event Audit in the Audit action items of this issue
  • remove status: needs calendar event audit label
  • close the tabs you just opened (wiki, spreadsheet, calendar)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Perform hackforla.org/VRMS Audit

Project specific steps (scroll down for CoP specific steps)

• [ ] Open Resource(s)
  • open resource 1.0
  • access https://www.hackforla.org/projects
  • locate your project card and access the project's page
  • view side by side
  • copy the URL for your project's page
  • on the Details sheet in resource 1.0, replace the text that says https://www.hackforla.org/projects/[Replace PROJECT-NAME], in B5 with the URL

    Compare meetings in the Zoom Setup spreadsheet against meetings that appear on the project's page

    In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the project's page. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match.

Access the hackforla.org/VRMS tab and complete the following

• [x] in Section 1, which is dependent on data in Section 4
  • if there is meeting data in Section 4, check that columns A and B are hidden (column C is the first column you will see)
  • if there is no meeting data in Section 4, check that the value in cell B2 is set to NO
• [x] in Section 3: Add Meeting Data from Section 4, find matching meetings in Section 4 and copy the data into columns H through K of Section 3 The values in column C, M and N will change as you perform this audit step
  • start with the first meeting in column D of Section 2 and find it's match in Section 4
  • copy the Meeting Name, Week Day, Start Time (PST), End Time (PST) and paste (Crtl+Shift+V) the values into columns H through K inSection 3`
  • if the meeting name is not in Section 4, leave the corresponding columns (H through K) empty in Section 3 for that meeting
• [x] in Section 4, indicate meetings that are on the project's page but not in the Zoom Setup spreadsheet
  • if there are any meetings in this section with no match in Section 2 (you did not copy the data into columns H through K of Section 3), select No in column L for that meeting

CoP specific steps

• [ ] Open Resource(s)
  • open resource 1.0
  • on the Details sheet in resource 1.0, replace the text that says https://www.hackforla.org/projects/[Replace PROJECT-NAME], in B5 with https://www.hackforla.org/communities-of-practice

    Compare meetings in the Zoom Setup spreadsheet against meetings that appear on the CoP's page

    In this step, you will determine if the meetings in the Zoom Setup Spreadsheet match with the CoP's page. There may be slight differences in the meeting names, meeting start/end times but if the majority of the data matches, consider the meetings a match.

Access the hackforla.org/VRMS tab and complete the following

• [ ] in Section 1, which is dependent on data in Section 4
  • if there is meeting data in Section 4, check that columns A and B are hidden (column C is the first column you will see)
  • if there is meeting data in Section 4, check that the value in cell B2 is set to NO
• [ ] in Section 3: Add Meeting Data from Section 4, find matching meetings in Section 4 and copy the data into columns H through K of Section 3 The values in column C, M and N will change as you perform this audit step
  • start with the first meeting in column D of Section 2 and find it's match in Section 4
  • copy the Meeting Name, Week Day, Start Time (PST), End Time (PST) and paste (Crtl+Shift+V) the values into columns H through K inSection 3`
  • if the meeting name is not in Section 4, leave the corresponding columns (H through K) empty in Section 3 for that meeting
• [ ] in Section 4, indicate meetings that are on the CoP's page but not in the Zoom Setup spreadsheet
  • if there are any meetings in this section with no match in Section 2 (you did not copy the data into columns H through K of Section 3), select No in column L for that meeting

Close out this task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Perform hackforla.org/VRMS Audit in the Audit action items of this issue
  • remove the status: needs hackforla.org/VRMS audit label
  • close the tabs you just opened (wiki, spreadsheet, website)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Create and send message to PMs via Slack

Create a new comment on the audit issue and paste the link into resource 2.0

• [x] Create the new comment
  • in the empty comment below this one, paste the following ### Post Audit Messaging heading
  • select the green Comment button
  • copy the ### Post Audit Messaging comment link (the comment you just created)
• [x] Paste comment link in resource 2.0
  • open resource 2.0 in a new tab
  • in the Details tab, paste it into cell B13

    Create Post Audit Messaging Document

    In this step, you will create a customized message to send to the PMs of the project or CoP, informing them of the next steps. Please note that you will NOT be sending emails, but will only copy the message shown in the preview and paste it into the Post Audit Messaging comment and then in Slack.

• [x] create the message
  • open Template for Post Audit Messaging (open in new tab)
  • in the right side panel, select the mail merge icon
  • in the panel that opens up, select Emails
  • choose SELECT next to Select recipients
  • type/paste the name of resource 2.0 in the search bar and select enter
  • link the spreadsheet by double clicking on the name or choosing SELECT
  • in the same Mail Merge panel, select the down arrow under Selected sheet and select MailMergeData
  • select NEXT
  • DO NOT select SEND EMAILS
  • select Preview
  • if the process works as expected, the text in all the {{ }} should be replaced by text relevant to the project or CoP
    See example

    
  • inside the preview window, copy the message (CRTL+A and then CRTL+C)
• [x] paste the message in the Post Audit Messaging comment in the issue
  • move to the window with the issue
  • select Edit for the Post Audit Messaging comment
  • paste the message in the comment (CRTL+V)
  • select Update Comment

    Send message on Slack

• [ ] copy the message created for Slack and sent it
  • from the comment you just updated, copy the message under Slack Messaging
  • in another window, access the appropriate project or CoP channel in the HackforLA Slack workspace
  • paste the copied text into a new message window and send the message

    Close out this task

• [x] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Create and send message to PMs via Slack in the Action items of this issue
  • remove status: needs post audit messaging label
  • add status: waiting on team feedback label
  • move the issue to the Questions/Review on the project board
  • close the tabs you just opened (wiki, document, message preview window and spreadsheet)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Post Audit Messaging

The Admin team has just finished an audit of the HackforLA.org’s calendars, Zoom accounts use, Zoom setup spreadsheet reservations and VRMS.

Please review the HackforLA.org: Meeting Data Audit and Reconciliation, Next Steps tab and do the items indicated in the Changes Required column, using the tutorials from the WIKI Calendaring How Tos for step by step instructions.

If there are changes to be made to any meeting, and once all the required changes for each meeting have been completed, please:

1. Check the box for Updated on the HackforLA.org: Meeting Data Audit and Reconciliation, Next Steps tab.

2. Write back to us by adding a comment on this issue and let us know you are done or contact us via the #admin Slack channel to set up a meeting if you have questions.

Slack Messaging

Message sent on Slack 2023-05-09 (not actually sent on slack, just used for testing)

@Christopher Kong The Admin team has created an issue re HackforLA.org’s calendaring.  Please review and follow the instructions in  https://github.com/hackforla/admin-calendaring/issues/10#issuecomment-1540411467

[ExperimentsInHonesty]

revised template text https://docs.google.com/document/d/1-KAndQE7Eh5qgxMnEWyzVlRGnWfgOsXcjNL3obCAY0E/edit

[ExperimentsInHonesty]

Create and send message to PMs via Slack

Create a new comment on the audit issue and paste the link into resource 1.0

• [ ] Create the new comment

  • in the empty comment below this one, paste the following heading

    
    ### Post Audit Messaging

  
  - select the green `Comment` button
  - copy the `### Post Audit Messaging` comment link (the comment you just created)

• [ ] Paste comment link in resource 1.0
  • open resource 1.0 in a new tab
  • in the Details tab, paste it into cell B14

    Create Post Audit Messaging Document

    In this step, you will create a customized message to send to the PMs of the project or CoP, informing them of the next steps. Please note that you will NOT be sending emails, but will only copy the message shown in the preview and paste it into the Post Audit Messaging comment and then in Slack.

• [ ] create the message
  • open Template for Post Audit Messaging (open in new tab)
  • click on USE TEMPLATE button
  • save it shared drive
  • rename
  • in the right side panel, select the mail merge icon
  • in the panel that opens up, select Emails
  • choose SELECT next to Select recipients
  • type/paste the name of resource 1.0 in the search bar and select enter
  • link the spreadsheet by double clicking on the name or choosing SELECT
  • select the down arrow under Selected sheet and select MailMergeData
  • select NEXT button
  • select Preview(DO NOT select SEND EMAILS)
  • if the process works as expected, the text in all the {{ }} should be replaced by text relevant to the project or CoP
    See example

    
  • inside the preview window, copy the message (CRTL+A and then CRTL+C)
• [ ] paste the message in the Post Audit Messaging comment in the issue
  • move to the window with the issue
  • select Edit for the Post Audit Messaging comment
  • paste the message in the comment (CRTL+V)
  • select Update Comment
• [ ] Check to make sure the link resolves to the Meeting Data Audit and Reconciliation Spreadsheet

  Send message on Slack

• [ ] copy the message created for Slack and sent it
  • from the comment you just updated, copy the message under Slack Messaging
  • in another window, access the appropriate project or CoP channel in the HackforLA Slack workspace
  • paste the copied text into a new message window and send the message

    Close out this task

• [ ] You are done with all the steps in this task. Close it out in the main body of the issue
  • check the box for Create and send message to PMs via Slack in the Action items of this issue
  • remove status: needs post audit messaging label
  • add status: waiting on team feedback label
  • move the issue to the Questions/Review on the project board
  • close the tabs you just opened (wiki, document, message preview window and spreadsheet)
  • hide this comment as RESOLVED, you are finished with this step

[ExperimentsInHonesty]

Post Audit Messaging

The Admin team has just finished an audit of the HackforLA.org’s calendars, Zoom accounts use, Zoom setup spreadsheet reservations and VRMS.

Please review the HackforLA.org: Meeting Data Audit and Reconciliation, Next Steps tab and do the items indicated in the Changes Required column, using the tutorials from the WIKI Calendaring How Tos for step by step instructions.

If there are changes to be made to any meeting, and once all the required changes for each meeting have been completed, please:

1. Check the box for Updated on the HackforLA.org: Meeting Data Audit and Reconciliation, Next Steps tab.

2. Write back to us by adding a comment on this issue and let us know you are done or contact us via the #admin Slack channel to set up a meeting if you have questions.

Slack Messaging

Message sent on Slack 2023-05-10

@Christopher Kong The Admin team has created an issue re HackforLA.org’s calendaring.  Please review and follow the instructions in  https://github.com/hackforla/admin-calendaring/issues/10#issuecomment-1542299673

[ExperimentsInHonesty]

PM

Problem

• Team Calendar Audit: meeting in Zoom Setup spreadsheet but not on team calendar
• Zoom Audit: name of meeting in Zoom account doesn't follow naming convention
• VRMS Audit: meeting in Zoom Setup spreadsheet but not on HfLA website

Solution

• [ ] Update Zoom Setup Spreadsheet: delete meeting from Zoom Setup Spreadsheet
• [ ] Update Zoom Account: rename meeting from PM Meeting HfLA Website to HfLA site: PM
• [ ] Update VRMS/hackforla.org: update the meeting in VRMS to add meeting