hackforla / admin-calendaring

This is an administrative repo to reconcile teams' calendaring and organization Zoom accounts.

GNU General Public License v2.0

0 stars 0 forks source link

Meeting Data Audit: New Template Test #12

Open Rabia2219 opened 1 year ago

Rabia2219 commented 1 year ago

Issue Template Details

Issue name: Meeting Data Audit: [Replace PROJECT NAME] Labels: feature: calendaring, prep, status: needs Slack audit document, status: needs Slack audit, status: needs Slack channel(s), status: needs audit spreadsheet, status: needs Zoom Setup data, status: needs automation formulas, status: needs calendar event audit, status: needs Zoom accounts audit, status: needs Slack audit, status: needs hackforla.org/VRMS audit, status: needs Slack reconciliation, status: needs post audit messaging, status: waiting on team feedback

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
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
- [x] 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

Action Items

Setup Access Through Proper Profile

Non PMs
- [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
- [ ] 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

[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 new profile browser.
[x] 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's drive in the list of shared drives, see Prerequisite

[x] access the Org Level Audits folder in the project's shared drive
[x] right click on the folder and select Add to starred

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 Slack Audit document (open in new tab)
[x] Add Slack channel(s) to the Slack Audit document (open in new tab)
[x] Create Meeting Data Audit and Reconciliation Spreadsheet (open in new tab)
[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
- [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
  - [x] right click on the folder and select Remove from starred

Admin Prep

[x] Import project's data from Zoom Setup spreadsheet
[x] Setup Automation Formulas 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

NOTE: There is autogenerated data in the spreadsheet you will be using so please do not edit any Auto Notes, Changes Required or any auto-filled columns

[x] Perform Calendar Event Audit
[x] Perform Zoom Accounts Audit
[x] Perform Slack Audit
[x] Perform hackforla.org/VRMS Audit
[ ] 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

Resources/Instructions

1.0 Zoom Setup Spreadsheet 2.0 New Template Test: Slack Audit Document 3.0 New Template Test: Meeting Data Audit and Reconciliation Spreadsheet

Rabia2219 commented 1 year ago

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 3.0
[ ] open resource 2.0
- [ ] add link to the Slack Audit document on the Details sheet, B5
[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] replace [Replace PROJECT INITIALS] with project's or CoP's initials
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
[x] choose move icon or file menu item, Move in the spreadsheet
[x] use back arrow (top left)
[x] choose Starred
[x] open the Org Level Audits folder
[x] choose Move here
[x] 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] 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

Rabia2219 commented 1 year ago

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 or 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] count the number of channels
[x] go to resource 2.0, page 2
- [x] if more than one channel, copy and paste the block of headings to a new page for each channel by copying from the top of page 2 to the end of the document, and then pasting on the empty page (page 3, page 4, etc.)
- [x] replace [channelname] for each block with the channel name you found on Slack
[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
Close out this task
[x] check the box for Add Slack channel(s) in the Action items of this issue
[x] close the tabs you just opened (wiki, document, Slack)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

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 3.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] replace [Replace PROJECT INITIALS] with project's or CoP's initials
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
[x] choose move icon or file menu item, Move in the spreadsheet
[x] use back arrow (top left)
[x] choose Starred
[x] open the Org Level Audits folder
[x] choose Move here
[x] choose Move
Option 1
[ ] 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] 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

Rabia2219 commented 1 year ago

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

Option 1
[x] choose move icon or file menu item, Move in the spreadsheet
[x] use back arrow (top left)
[x] choose Starred
[x] open the Org Level Audits folder
[x] choose Move here
[x] choose Move
Option 1
[ ] 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
Paste the spreadsheet URL in the Details tab
[x] copy the sheet URL starting from https till the / before edit
[x] paste it into cell B7 in the Details tab of the spreadsheet
Paste Slack audit URL in the Details tab
[x] open resource 2.0
[x] copy the document URL
[x] paste it into cell B5 in the Details tab of the spreadsheet
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

Rabia2219 commented 1 year ago

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
  copy sheet into resource 4.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 2.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
  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
  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
  Close out this task
[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] close the tabs you just opened (wiki, spreadsheets)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Setup Automation Formulas

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

Setup the Overall Audit Sheet

Formatting
[x] change the header color to #38761d (the second to last green in the Fill color option)
[x] change the header font color to white
[x] change the header name in Col C to Name of Meeting in Zoom Setup Spreadsheet

Remove Validation in Column S
[x] select column S
[x] right click and select Data Validation
[x] select the rule for column S
[x] select Remove Rule
[x] x out of the Data validation rules panel

Hide and Insert Columns
[x] hide columns D, N, O, U and V
[x] insert 4 columns to the right of column T
[x] name column U Auto Notes

Add Formula

[x] copy and paste the following formula in cell U2

=IF(AND(C2='hackforla.org-VRMS'!C4,C2='Calendar Event Audit'!A3,C2='Zoom Accounts Audit'!B3,C2='Slack Audit'!B3),'hackforla.org-VRMS'!K4&Char(10)&'Calendar Event Audit'!S3&Char(10)&'Calendar Event Audit'!S23&Char(10)&'Zoom Accounts Audit'!K3&Char(10)&'Zoom Accounts Audit'!K23&Char(10))

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

In Section 1

[x] check that column A has been auto filled
[x] Delete all empty rows except one
[x] check that the values for column R changed to YES
[x] check that the formula in column S is copied till the last filled row in this section

In Section 3
[x] check that columns A,C,D,E,G and I have been auto filled
[x] delete all empty rows except the last one
[x] check that the formula in column S is copied till the last filled row in this section

Setup the hackforla.org-VRMS Sheet (This sheet is divided into 3 sections)

In Section 2
[x] check that column A has been auto filled
[x] delete all empty rows except the last one
[x] check that the values for column J changed to YES
[x] check that the formula in column K is copied till the last filled row in this section

In Section 3
[x] check that the formula in column K is copied till the last row in this section

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

In Section 1

Filter out relevant project data
[x] select the 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
[x] select the data starting from A3 till column H and the last row above row 54 (till the green border of the filter)
[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 3
[x] check that column B through H have been auto filled
[x] delete all empty rows except the last one
[x] check that the formula in column K is copied till the last filled row in this section

In Section 1 again
[x] 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) in Section 3
- [x] if there is a meeting that is not listed in column A, add a missing row in it's place
[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 empty rows except the last one

Setup the Slack Audit sheet
[x] access resource 2.0
[x] view side by side with the Slack Audit sheet
[x] check that columns A through D have been auto filled

Add channel name(s)

Please see the before and after screenshots below so you have an understanding of what needs to be done.

Before

After
repeat the following steps for every channel name in resource 2.0
- [x] copy the channel name and paste it in the appropriate row in column A in the Slack Audit sheet (e.g. [Replace channelname1] will be replaced with the first channel name in resource 2.0)
- [x] copy the channel name in column A for each meeting name
- [x] delete the extra [Replace channelname[#]] rows if any
- [x] copy columns B, C and D
- [x] paste it in column B for the next channel
[x] after all channel names have been added, delete all empty rows except the last one

Add Remaining Formulas to Overall Audit Sheet

Access the Overall Audit tab

In column F,
[x] add an = sign in F2
[x] access the Zoom Accounts Audit tab
[x] select the first cell in column I in Section 3: Data Pulled from Zoom Setup Spreadsheet
[x] press Enter to save
[x] 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,
[x] add an = sign in G2
[x] access the Zoom Accounts Audit tab
[x] select cell A3
[x] press Enter to save
[x] 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 H,
[x] add an = sign H2
[x] access the Calendar Events Audit tab
[x] select the first cell in column J in Section 3: Data Pulled from Zoom Setup Spreadsheet
[x] press Enter to save
[x] 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,
[x] add an = sign I2
[x] access the Calendar Events Audit tab
[x] select cell B3
[x] press Enter to save
[x] 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,

[x] copy and paste the following formula in cell J2

=(IF(OR('Slack Audit'!E3="YES",'Slack Audit'!I3="YES",'Slack Audit'!M3="YES",'Slack Audit'!Q3="YES",'Slack Audit'!U3="YES"),"YES","NO"))

[x] press Enter to save
[x] 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,

[x] copy and paste the following formula in cell K2

=TEXTJOIN(",",TRUE,IF('Slack Audit'!E3="YES",'Slack Audit'!F3,""),IF('Slack Audit'!I3="YES",'Slack Audit'!J3,""),IF('Slack Audit'!M3="YES",'Slack Audit'!N3,""),IF('Slack Audit'!Q3="YES",'Slack Audit'!R3,""),IF('Slack Audit'!U3="YES",'Slack Audit'!V3,""))

[x] press Enter to save
[x] 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,
[x] add an = sign L2
[x] access the hackforla.org-VRMS tab
[x] select cell D4
[x] press Enter to save
[x] 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,
[x] add an = sign M2
[x] access the hackforla.org-VRMS tab
[x] select cell E4
[x] press Enter to save
[x] copy the formula down to the last row by dragging the tiny blue box at the end of the cell all the way down

Close out this task
[x] check the box for Setup Automation Formulas for the Audit in the Action items of this issue
[x] close the tabs you just opened (wiki, spreadsheet)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Create Slack Audit document

[x] go to Template for Slack Audit (open in new tab)
[x] select USE TEMPLATE button
[x] add the newly created document's link to resource 2.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 CRTL+H or CMD+SHIFT+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
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 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] 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 and document)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

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] count the number of channels
[x] go to resource 2.0, page 2
- [x] if more than one channel, copy and paste the block of headings to a new page for each channel by copying from the top of page 2 to the end of the document, and then pasting on the empty page (page 3, page 4, etc.)
- [x] replace [channelname] for each block with the channel name you found on Slack
[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
Close out this task
[x] check the box for Add Slack channel(s) in the Action items of this issue
[x] remove status: needs Slack channel(s) label
[x] close the tabs you just opened (wiki, document, Slack)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

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

Rabia2219 commented 1 year ago

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] 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 and access it
[x] find resource 3.0 in the folder and access it
[x] copy the file name
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] access resource 3.0 and 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)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Set up Automation Formulas

[ ] open resource 3.0 in a new window
[ ] 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

[ ] change the column name in Col C to Name of Meeting in Zoom Setup Spreadsheet
[ ] change the column name in Col E to 'In Zoom Setup Spreadsheet'
[ ] remove the sheet filter by selecting the green funnel in the task bar
Remove Validation in Column J
[ ] 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
Hide and Insert Columns
[ ] hide columns D, L, M*
[ ] insert 6 columns to the right of column U
[ ] name column V Auto Notes
[ ] name column W Changes Required
Add Formula

[ ] copy and paste the following formula in cell V2, and copy it till the last row (select cell V2, hover over the small blue square, 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),""))

[ ] copy and paste the following formula in cell W2, and copy it till the last row (select cell V2, hover over the small blue square, 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

[ ] select cell A3, select enter twice (the first meeting name should appear)
[ ] copy the formula till the last row with a meeting name
[ ] select cell B3, select enter twice (the error should disappear)
[ ] copy the formula till the last row with a meeting name
[ ] select cell B3, select enter twice (the error should disappear)
[ ] copy the formula till the last row with a meeting name
[ ] 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

[ ] 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
[ ] delete all rows without any meeting names except for one
[ ] check that the values for column T changed to YES
[ ] check that the formula in column U is copied till the last row with a meeting name in this section
[ ] check that the formula in column U is copied till the last row with a meeting name in this section
[ ] check that column W has been auto filled
In Section 3
[ ] check that columns A,D,E,F,H and J have been auto filled
[ ] delete all rows without any meeting names except for one
In cell B1
[ ] 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

[ ] select the down arrow next to Team in column H
[ ] under Filter by values, search for your project or CoP name or initials or short name*
[ ] unselect your project name so that the checkmark next to it disappears
[ ] select OK
[ ] 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)
[ ] right click and select Delete selected rows
[ ] select the funnel next to Team in column G
[ ] under Filter by values, select Select All (only your project's name should appear)
[ ] select OK
[ ] undo the sheet filter by selecting the green funnel in the tool bar
[ ] in column K, copy the formula till the last row with data
In Section 3
[ ] check that column B through H have been auto filled
[ ] 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) of meetings that sound similar. Some cells may turn red, ignore that for now.
[ ] move the rows up or down to place a meeting in the right order
[ ] if there is a meeting that is not listed in column A, add a missing row in it's place In this step, formulas will be added
[ ] add an = sign before the function in cell B2
[ ] press Enter at the end of the formula to save
[ ] check that the values for column I changed to YES
[ ] delete all rows without data except for one
[ ] paste the following formula in column J and copy it till the last row with data
```
=IF(A4<>"","YES","NO")
```

- [ ] 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")

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

=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,""))

- [ ] paste the following formula in column M

=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
- [ ] 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
- [ ] check that column C, D, E and F have been auto filled
- [ ] delete all rows without data except for one
- [ ] check that the values for column J changed to `YES`
- [ ] check that the formula in columns K and L is copied till the last filled row in this section
#### In Section 3
- [ ] 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
- [ ] 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>

- [ ] 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)
- [ ] repeat the following for each remaining channel name
   - [ ] copy the channel name from the `Details` tab and paste it into the next empty cell in column A of the `Slack Audit` sheet
   - [ ] copy the same channel name into the cells below as many times as the first channel name/number of meetings in column B
- [ ] 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),"")

- [ ] after all channel names have been added, - [ ] delete all rows without data except for one

_You are done with this tab._

### Add Remaining Formulas to `Overall Audit` Sheet
Access the `Overall Audit` tab
#### In column N,
- [ ] add an `=` sign in N2
- [ ] access the `Zoom Accounts Audit` tab
- [ ] select the first cell in column J in Section 1
- [ ] 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 O,
- [ ] add an `=` sign in O2
- [ ] access the `Zoom Accounts Audit` tab
- [ ] select cell A4
- [ ] 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 P,
- [ ] add an `=` sign P2
- [ ] access the `Calendar Events Audit` tab
- [ ] select cell B4
- [ ] 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 Q,
- [ ] add an `=` sign Q2
- [ ] access the `Calendar Events Audit` tab
- [ ] select cell C4
- [ ] 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 R,
- [ ] copy and paste the following formula in cell R2*

=IF(OR('Slack Audit'!E3="YES",'Slack Audit'!I3="YES",'Slack Audit'!M3="YES",'Slack Audit'!Q3="YES",'Slack Audit'!U3="YES"),"YES","NO")

- [ ] 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 S,
- [ ] copy and paste the following formula in cell S2*

=TEXTJOIN(",",TRUE,IF('Slack Audit'!G3="YES",'Slack Audit'!H3,""),IF('Slack Audit'!K3="YES",'Slack Audit'!L3,""),IF('Slack Audit'!O3="YES",'Slack Audit'!P3,""),IF('Slack Audit'!S3="YES",'Slack Audit'!T3,""),IF('Slack Audit'!W3="YES",'Slack Audit'!X3,""))


- [ ] 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 T,
- [ ] add an `=` sign T2
- [ ] access the `hackforla.org-VRMS` tab
- [ ] select cell G
- [ ] 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 U,
- [ ] add an `=` sign U2
- [ ] access the `hackforla.org-VRMS` tab
- [ ] select cell H4
- [ ] 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
### Close out this task
- [x] check the box for `Setup Automation Formulas for the Audit` in the Action items of this issue
- [x] remove `status: needs automation formulas` label
- [x] close the tabs you just opened (wiki, spreadsheet)
- [x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Perform Calendar Event Audit

Access the team's calendar

[ ] if you are the pm on the project, open the project's email account and go to the calendar
[ ] Else, 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 3.0 in a new window
- [ ] 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.
  
  In Section 1
- [ ] 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
- 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
- [ ] fill out columns C through S (skip columns I and K) for meetings that appear on the team's calendar but are not in Section 3: Data Pulled from Zoom Setup Spreadsheet
  Close out this task
- [x] check the box for Perform Calendar Event Audit in the 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

Rabia2219 commented 1 year ago

Perform Zoom Accounts Audit

Open resource

[ ] open resource 3.0 in a new window
[ ] 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.

In Section 1
[ ] Confirm that there are no #REF! or #N/A errors.
In Section 2
[ ] Confirm that there are no #REF! or #N/A errors.
[ ] Confirm that if 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
[x] check the box for Perform Zoom Accounts Audit in the 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

Rabia2219 commented 1 year ago

Perform hackforla.org/VRMS Audit

Project specific steps

[ ] open resource 3.0
[ ] access https://www.hackforla.org/projects
[ ] locate your project card and access the project's page
[ ] copy the URL
[ ] on the Details sheet in resource 3.0, paste the URL in place of https://www.hackforla.org/projects/[Replace PROJECT-NAME], B6
Add meeting data

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.

In Section 1
[ ] access the project's page
[ ] access the hackforla.org/VRMS tab in resource 3.0 (There are 3 sections in this sheet)
[ ] view side by side
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
[ ] for each meeting in column C, fill out columns D through I using the information on the project's page
- if any meeting in column C is not on the project's page, fill only column G by selecting "NO"
  In the Section 3
[ ] fill out columns D through I for meetings that appear on the project's page but are not in column C of Section 1
CoP specific steps
[ ] open resource 3.0
[ ] 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
Add meeting data

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.
[ ] open the URL https://www.hackforla.org/communities-of-practice
[ ] find the CoP
[ ] access the hackforla.org/VRMS tab (There are 3 sections in this sheet)
[ ] view side by side
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
[ ] record the data for each meeting on the CoP's page that matches the meeting in column C
- if any meeting in column C is not on the CoP's page, fill only column G by selecting "NO"
  In Section 3
[ ] fill out columns D through I for meetings that appear on the CoP's page but are not in column C of Section 1
Close out this task
[x] check the box for Perform hackforla.org/VRMS Audit in the 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

Rabia2219 commented 1 year ago

Create and send message to PMs via Slack

Create a comment on the audit issue

[x] in the empty comment below this one, paste the following ### Post Audit Messaging heading
[x] select the green Comment button
[x] copy the ### Post Audit Messaging comment link
Paste comment link in Meeting Data Audit and Reconciliation
[x] open resource 3.0 (in a new tab)
[x] paste it into cell B11 in the Details tab
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 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 3.0 in the search bar and select enter
- link the spreadsheet by double clicking on the name or choosing SELECT
- in the right side panel, select the down arrow under Selected sheet and select MailMergeData
- select NEXT
- DO NOT select SEND EMAILS
- select Preview
- inside the preview window, copy the message (CRTL+A)
[x] paste the message in the Post Audit Messaging comment
- select Edit for the Post Audit Messaging comment in the issue
- paste the message in the comment (`CRTL+V)
- select Update Comment
  Send message on Slack
[x] copy the message under Slack Messaging from the Post Audit Messaging comment
[x] access the appropriate project or CoP channel on Slack
[x] paste the copied message
[x] send the message
Close out this task
[x] check the box for Create and send message to PMs via Slack in the Action items of this issue
[x] remove status: needs post audit messaging label
[x] move the issue to the Questions/Review on the project board
[x] close the tabs you just opened (wiki, document and spreadsheet)
[x] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Post Audit Messaging

The Admin team has just finished an audit of the HackforLA.org’s calendars, Zoom accounts use, Zoom setup spreadsheet reservations, VRMS and Slack references (pins, bookmarks, topics, reminders).

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.

When each meeting has all its changes completed, please

Check the box for Updated on the HackforLA.org: Meeting Data Audit and Reconciliation, Next Steps tab.
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-03-27

@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/12#issuecomment-1483425899

Rabia2219 commented 1 year ago

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 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 and send message to PMs via Slack in the Action items of this issue
[x] Remove status: needs post audit messaging label
[ ] close the tabs you just opened (wiki, spreadsheet and document)
[ ] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

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] access resource 3.0 and 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)
- [ ] select the white down arrow next to Project Name in column A
- [ ] under Filter by values, search for your project or CoP name
- [ ] unselect your project or CoP name so that the checkmark next to it disappears
- [ ] select OK
- [ ] select the data starting from the first cell in column A all the way to the last cell in column U
- [ ] right click and select Delete selected rows
- [ ] select the green funnel next to Project Name in column A
- [ ] under Filter by values (only your project or CoP name should appear), select Select All
- [ ] select OK
  Close out this task
[ ] remove status: needs Zoom Setup data label
[ ] check the box for Import project's data from Zoom Setup spreadsheet in the Action items of this issue
[ ] close the tabs you just opened (wiki, spreadsheets)
[ ] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Set up Automation Formulas

Open Resource(s)

[ ] open resource 3.0 in a new window
[ ] 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

[ ] change the column name in Col C to Name of Meeting in Zoom Setup Spreadsheet
[ ] change the column name in Col E to 'In Zoom Setup Spreadsheet'
[ ] 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
[ ] 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
[ ] name column N Auto Notes
[ ] name column O Changes Required
Add Formula

[ ] 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),""))

[ ] 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

[ ] select cell A3, select enter twice (the first meeting name should appear)
[ ] copy the formula till the last row with a meeting name
[ ] select cell B3, select enter twice (the error should disappear)
[ ] copy the formula till the last row with a meeting name
[ ] select cell C3, select enter twice (the error should disappear)
[ ] copy the formula till the last row with a meeting name
[ ] 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

[ ] 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
[ ] delete all rows without any meeting names except for one
[ ] check that the values for column T changed to YES
[ ] check that the formula in column U is copied till the last row with a meeting name in this section
[ ] check that the formula in column U is copied till the last row with a meeting name in this section
[ ] check that column W has been auto filled
In Section 3
[ ] check that columns A,D,E,F,H and J have been auto filled
[ ] delete all rows without any meeting names except for one (SHIFT+CRTL+down arrow will select all empty rows)
In cell B1
[ ] 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

[ ] select the down arrow next to Team in column H
[ ] under Filter by values, search for your project or CoP name or initials or short name*
[ ] unselect your project name so that the checkmark next to it disappears
[ ] select OK
[ ] 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)
[ ] right click and select Delete selected rows
[ ] select the funnel next to Team in column G
[ ] under Filter by values, select Select All (only your project's name should appear)
[ ] select OK
[ ] 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, the values in this section will keep changing. Please ignore them. If by the last action item for this tab, there are still #N/A errors, that is expected.

In Section 3

[ ] check that column B through H have been auto filled
[ ] 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.
[ ] move the rows up or down to place a meeting in the right order (to match with column B of Section 3)
[ ] 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.

[ ] add an = sign before the function in cell B2
[ ] press Enter at the end of the formula to save
[ ] check that the values for column I changed to YES
[ ] 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.

[ ] paste the following formula in column J and copy it till the last row with data
```
=IF(A4<>"","YES","NO")
```

- [ ] 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")

- [ ] 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,"")))

- [ ] 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
- [ ] 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
- [ ] check that column C, D, E and F have been auto filled
- [ ] delete all rows without data except for one
- [ ] check that the values for column J changed to `YES`
- [ ] delete all rows without any data except for one
- [ ] check that the formula in columns K and L is copied till the last filled row in this section
#### In Section 3
- [ ] 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
- [ ] 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>

- [ ] 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)
- [ ] repeat the following for each remaining channel name
   - [ ] 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`).
   - [ ] 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)
- [ ] 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),"")


- [ ] 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._

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

Rabia2219 commented 1 year ago

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
[ ] Else, 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 3.0 in a new window
- [ ] 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
  
  In this section, 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
    
    In this section, 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.

[ ] 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

Rabia2219 commented 1 year ago

Perform Zoom Accounts Audit

Open resource

[ ] open resource 3.0 in a new window
[ ] 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
[ ] 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, if there are no discrepancies between meeting in the Zoom Setup spreadsheet and the Zoom accounts, then #N/A errors are expected to appear throughout the first row.
[ ] Confirm that there are no #REF! errors. (contact Admin via Slack in the #admin channel if there are any)
[ ] 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.
[ ] 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

Rabia2219 commented 1 year ago

Perform Slack Audit

Open resources

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

Repeat for each channel name in resource 2.0
[ ] in Hack for LA's Slack workspace, search for the channel name
collect the following data for the channel (if you are not a member of the channel, you can join temporarily to collect the data and then leave)
- [ ] Reminders
  - [ ] go to the channel
  - [ ] type /remind list in a comment and hit enter
  - [ ] copy the resulting text
  - [ ] paste it under the Reminders for that channel
  - [ ] write none if there's no data
- [ ] Pins
  - [ ] click on the pin icon in the top left of the channel window (no pin icon = no pins)
  - [ ] copy the messages
  - [ ] paste under the Pins for that channel
  - [ ] write none if there's no data
- [ ] Bookmarks (if there are any bookmarks in the channel, they will appear next to the pinned items)(3 dots = there are more)
  - [ ] 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)
  - [ ] right click on the bookmark, copy link and link it to the resource name you just wrote
- [ ] Topic
  - [ ] 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 (add topic/no text = no topic)
  - [ ] paste under the Topic for that channel
  - [ ] for emojis
  - [ ] right click on the emoji, copy image, paste it with corresponding topic
  - [ ] write none if there's no data
- [ ] Description
  - [ ] 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 (add description/no text = no description)
  - [ ] paste under the Description for that channel
  - [ ] for emojis
  - [ ] right click on the emoji, copy image, paste it with corresponding description
  - [ ] write none if there's no data
[ ] refresh the Table of Contents (TOC) on the first page
[ ] add the appropriate emojis to each category
- [ ] go to the Legend on Page 1 of the document
- [ ] 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 👁️‍🗨️)
- [ ] 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 🚫)
- [ ] refresh the Table of Contents (TOC) on the first page The TOC will look something like this when you are done
[ ] update the Last Updated date in the footer
[ ] 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.
[ ] check the box for Perform Slack Audit in the Audit action items of this issue
[ ] remove the status: needs Slack audit label
[ ] 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
[ ] close the tabs you just opened (wiki, document, Slack)
[ ] hide this comment as RESOLVED, you are finished with this step

Rabia2219 commented 1 year ago

Perform hackforla.org/VRMS Audit

Project specific steps (scroll down for CoP specific steps)

Open Resource(s)

[ ] open resource 3.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 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.
[ ] 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.
[ ] 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.

[ ] 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

Rabia2219 commented 1 year ago

Perform Slack Audit

Open resources

[ ] open resource 2.0
[ ] access Hack for LA's Slack workspace through https://hackforla.slack.com/ (use the method you regularly use)
[ ] 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
- [ ] 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
- [ ] 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
- [ ] 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
- [ ] 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
- [ ] 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
[ ] add the appropriate emojis to each category
- [ ] go to the Legend on Page 1 of the document
- [ ] 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 👁️‍🗨️)
- [ ] 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 🚫)
- [ ] refresh the Table of Contents (TOC) on the first page The TOC will look something like this when you are done
[ ] update the Last Updated date in the footer
[ ] 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.

[ ] check the box for Perform Slack Audit in the Audit action items of this issue
[ ] remove the status: needs Slack audit label
[ ] 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
[ ] close the tabs you just opened (wiki, document, Slack)
[ ] hide this comment as RESOLVED, you are finished with this step

hackforla / admin-calendaring

Meeting Data Audit: New Template Test #12

Issue Template Details

Overview

Prerequisite

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

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

Action Items

Setup Access Through Proper Profile

Access GitHub

Add Org Level Audits folder to Starred

Prep

Admin Prep

Audit

Reconciliation

Recommendations and next steps

Resources/Instructions

Create Slack Audit document

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

Option 1

Option 2

Close Out this Task

Add Slack channels

Open resources

Get channel names

Close out this task

Create Recommendations & Next Steps document

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

Option 1

Option 1

Close Out this Task

Create Meeting Data Audit and Reconciliation Spreadsheet

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

Option 1

Option 1

Paste the spreadsheet URL in the Details tab

Paste Slack audit URL in the Details tab

Close Out this Task

Import project's data from Zoom Setup spreadsheet

create a filter for your project

copy sheet into resource 4.0

Remove irrelevant (other projects') data

Undo the filter in Zoom Setup (resource 1.0)

Close out this task

Setup Automation Formulas

Setup the Overall Audit Sheet

Formatting

Remove Validation in Column S

Hide and Insert Columns

Add Formula

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

In Section 1

In Section 3

Setup the hackforla.org-VRMS Sheet (This sheet is divided into 3 sections)

In Section 2

In Section 3

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

In Section 1

In Section 3

In Section 1 again

Setup the Slack Audit sheet

Add channel name(s)

Add Remaining Formulas to Overall Audit Sheet

In column F,

In column G,

In column H,

In column I,

In column J,

In column K,

In column L,

In column M,

Close out this task

Create Slack Audit document

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

Close Out this Task

Add Slack channels

Open resources

Get channel names

Close out this task

Create Meeting Data Audit and Reconciliation Spreadsheet

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

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

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

Paste the spreadsheet URL in the `Details` tab

Paste Slack audit URL in the `Details` tab

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

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

Paste the spreadsheet URL in the `Details` tab

Paste Slack audit URL in the `Details` tab

Paste Slack channel(s) in the `Details` tab

Set up the `Overall Audit` Sheet

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

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

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

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

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

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

Paste the spreadsheet URL in the `Details` tab

Paste Slack audit URL in the `Details` tab

Paste Slack channel(s) in the `Details` tab