Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision |
howto:netspreadsheet [2020/04/09 20:24] – va7fi | howto:netspreadsheet [2021/04/06 08:50] – [Notes for Admins] va7fi |
---|
~~NOTOC~~ | ====== Net Roll Call Spreadsheet ====== |
| |
====== Net Logging Spreadsheet ====== | <WRAP center round alert> |
| The Google Spreadsheet can only be edited by the owners. Everyone else needs to download the Excel or LibreOffice version for offline use. |
<WRAP center round alert 60%> | |
Do not use the online version is someone is already using it. You'll be interfering with them. | |
</WRAP> | </WRAP> |
| |
| ===== LibreOffice or Excel ===== |
| If you don't have LibreOffice or Excel on your computer, you'll want to install [[https://libreoffice.org/ |LibreOffice]] first, which is a free and open source office suite. |
| |
| |
| ===== Downloading The File ===== |
| Currently, the file is not public, contact [[va7fi@rbox.me |Patrick, VA7FI]] if you'd like access. |
| |
| Once you have the link, you can download it as a Microsoft Excel file or OpenDocument File. Download the latter format if you use LibreOffice. |
| {{ :howto:download.png }} |
| |
| The date in the title of the file represents the last time the spreadsheet was updated. You'll want to download a fresh copy from time to time to take advantage of new entries that are added periodically. |
| |
| |
===== Basic Idea ===== | ===== Basic Idea ===== |
| |
* This is a Google spreadsheet that can be used directly in the browser or downloaded as an Excel or OpenOffice file for offline use. | The spreadsheet has a few tabs that can be selected at the bottom of the page: {{ :howto:tabs.png }} |
| |
* The sheet has two tabs that can be selected at the bottom of the page: {{ :howto:tabs.png }} | |<100% 200px - >| |
* ''Data'' contains the actual information about a few hundreds of hams. | ^Local Data |contains the actual information about a few hundred hams in the Vancouver Island area.| |
* ''LiveLogSheet'' is the "search / log" page. | ^LogSheet (Local + ISED) |is the "search / log" page used during the net.| |
| ^LogSheet (Local) |is a lighter version of the previous tab (for slower machines) that doesn't use ISED database.| |
| ^LogSheet (ISED) |is a version that only searches the ISED database. This could be useful for cross Canada nets.| |
| ^Instructions |links to this page.| |
| ^What's New |lists the recent updates to the spreadsheet (excluding edits to the "Local Data" sheet). | |
| ^ISED Data |is a hidden tab that contains over 80,000 entries from the [[http://www.ic.gc.ca/eic/site/025.nsf /eng/h_00004.html |ISED]] website. The last update uses data from Dec 20, 2020.| |
| |
===== The Data Tab ===== | |
| <WRAP center round important 80%> |
| The most important thing to know is that <fc #008000>you can type in and delete cells in green</fc>, but you should <fc #ff0000>not edit or delete cells in orange</fc>. |
| </WRAP> |
| |
| |
| ===== The Local Data Tab ===== |
{{ :howto:datatab.png }} | {{ :howto:datatab.png }} |
| {{:howto:localdatatab.png}} |
| |
The Data tab contains all the information about each ham operator and a few formulas to flag important dates. This sheet is <fc #ff0000>NOT</fc> the sheet you'll be using during the net, it's a "database": | The ''Local Data'' tab contains information about each ham operator and a few formulas to flag important dates. This sheet is <fc #ff0000>NOT</fc> the sheet you'll be using during the net, it's a sort of "database": |
* Column A is hidden and contains formulas that the other tab needs. | |<100% 11em >| |
* <wrap todo>Columns B -- M can be edited directly</wrap> (that's why the cells are in green). | ^Column(s) ^Edit ^Details ^ |
* Note that in Columns F, we've also added the callsign's suffix when a spouse is also a ham operator (ex.: "Jaqueline **JET**"). This makes logging spouses easier. | |Column A |<fc #ff0000>✘</fc> |Is hidden and contains formulas that the other tab needs. | |
* Columns E, G, I, and K are dates that should be entered as so '' <wrap mark hi>'</wrap>JAN 1''. <wrap tip>Note the apostrophe (') at the beginning to force the proper formatting.</wrap> | |Columns B -- M |✔ |Contains information about ham operators. | |
* <wrap important>Column N contains a formulas and should NOT be edited</wrap>. | |Columns E, G, I, K |✔ |Are dates that should be entered as so '' 'JAN 1''. The apostrophe (') is important. | |
| |Columns N -- P |<fc #ff0000>✘</fc> |Contains formulas and should NOT be edited. | |
| |
| {{ :howto:sheetfilter.png}} |
| |
To add a new entry, see the [[#adding_names |Adding Names]] section below. | To add a new entry, see the [[#adding_names |Adding Names]] section below. |
| |
==== Birthdays and Anniversaries ==== | ==== Birthdays and Anniversaries ==== |
| To see who is having a birthday or anniversary, filter column N or O (but not both at the same time): |
One quick way of seeing if there are any birthdays or anniversaries before starting the net is to filter column N: {{ :howto:sheetfilter.png?200}} | * Click on the upside-down triangle on the right of ''Today?'' in Column N or ''This Week?'' in Column O. |
* Click on the upside-down triangle on the right ''Today?'' in Column N. | * Remove the check mark on the left of ''(empty)''. \\ Under Microsoft Excel, it's called ''(Blanks)''. |
* Remove the check mark on the left of ''(blank)'' | |
* Press ''OK'' | * Press ''OK'' |
| |
What this will do is only show the lines that have a date of "today" written anywhere in the data sheet. All you have to do is note the names and callsign of these people to acknowledge them during the preamble. | This will show only the lines of people that have a birthday or anniversary on the day or week. Note the names and callsign of these people to acknowledge them during the preamble. |
| |
Once you've got that information, you can re-check the ''(blank)'' lines to unhide them. | Once you've got that information, you can re-check the ''(empty)'' lines to unhide them. |
| |
\\ | |
\\ | |
\\ | |
\\ | |
| |
===== The LiveLogSheet Tab ===== | ===== The LogSheet Tabs ===== |
| {{:howto:logsheettabs.png}} |
| |
| The three ''LogSheet (x)'' tabs are different ways of searching / logging checkins. The main differences between them are: |
| * ''Logsheet (Local + ISED)'': |
| * This is the tab that most people will use for nets in the Vancouver Island area. |
| * It searches both the ''Local Data'' and the ''ISED Data''. |
| * It requires only the suffix of the callsign if the entry in the ''Local Data'' tab is unique. |
| * ''Logsheet (Local)'': |
| * It's very similar to the previous tab except it only searches the ''Local Data'' so it's faster for slower computers or mobile devices. |
| * ''Logsheet (ISED)'': |
| * Searches only the ''ISED Data''. |
| * Useful for trans Canada wide nets. |
| * The full callsign must be used. |
| |
| Using ''Logsheet (Local + ISED)'' as an example, during the net, all you need to do is type the suffix in Column A (**not** case sensitive). |
{{ :howto:livelogsheet.png }} | {{ :howto:livelogsheet.png }} |
| |
The LiveLogSheet tab is really the meat of this spreadsheet. | * In the first example ''aa'' was entered twice successfully. The second entry was flagged as a duplicate and was not counted in the total checkins at the top in cell E1. |
* During the net, all you need to do is type the suffix in Column A (**not** case sensitive). | * When ''jh'' was entered in the suffix, the callsign asked for a ''Prefix?'' because two callsigns have the same suffix. Once the prefix is entered, the spreadsheet is able to fill in the rest of the information from the ''Local Data''. |
* Column B can be left blank unless there are two callsigns with the same suffix (ex. VA7DUO and VE7DUO). If that's the case, Column C will ask you to add the prefix in Column B. Most of the time, the prefix is not needed. | * When ''aaa'' is entered, it's not found in the ''Local Data'' so it asks for the ''Prefix'' to look it up in the ''ISED Data''. |
* Column C will either give you: | * When ''wek'' is entered, it's not found in either databases so it returns ''Not Found''. |
* The callsign if a match is found in the ''Data'' tab. | |
* The message ''Not Found'' if the suffix was not found in the ''Data'' tab. If that's the case, ask the operator to give you their information, make a note, and enter it in the ''Data'' tab at the end of the net for next time. | |
* The message ''Prefix?'' if there are multiple callsigns with the same suffix. So far, VA7DUO and VE7DUO are the only two callsigns with the same suffix. | |
* The other columns search for the callsign in Column C and fill in the rest of the information. These columns <fc #ff0000>should NOT be edited.</fc> | |
* Cell P1 shows the total number of checkins | |
| |
When the net is done, simply delete what you typed in column A and B (<fc #ff0000>NOT what appeared in the other columns</fc>) | Here are a few more details: |
| |
| * The cells in orange <fc #ff0000>should NOT be edited or deleted.</fc> |
| * Information from the ''ISED'' tab will have "(ISED)" written in the cells (ex. VE7AAA) |
| * Cell E1 shows the total number of unique checkins. |
| * The top shows the number of Birthdays and Anniversaries, which are also flagged in column O. |
| |
| When the net is done, simply delete what you typed in column A and B (<fc #ff0000>NOT what appeared in the other columns</fc>). |
| |
| A useful trick in LibreOffice is to freeze the first two rows so that as you go down the list, the header is always visible. To do this: Select Cell A3 -> ''View'' -> ''Freeze Rows and Columns'': |
| {{ :howto:freezeline.png }} |
| |
| |
| ** Note that I made up Richard's (VA7AA) birthday for the sake of illustration. |
| |
| |
| ===== Android Tablets/Phones ===== |
| The spreadsheet was made to be used offline on computers, but some people might still want to use it on their tablets. Here as some instructions on how to do that for Android devices. |
| |
| The first steps are to open the spreadsheet and make a copy of it to your "Sheets" app. You will need a Google Account to do this: |
| |
| * Open the link in the mobile browser. |
| * Click on ''USE THE APP'' (you need "Sheets" installed). |
| * Click on the three dots on the top right corner. |
| |
| <WRAP centeralign> |
| {{:howto:mobile_01.jpg?300}} {{:howto:mobile_02.jpg?300}} |
| </WRAP> |
| |
| * Click on ''Make a copy''. |
| * Add the word "Copy" to the end of the name to distinguish it from the original. |
| |
| <WRAP centeralign> |
| {{:howto:mobile_03.jpg?300}} {{:howto:mobile_04.jpg?300}} |
| </WRAP> |
| |
| * You are now using your copy, which you can edit. |
| * At the bottom, select ''LogSheet (Local)'', which doesn't query the ''ISED'' tab and is quicker for mobile devices. |
| * In your Sheets app, you should now see two versions: |
| * The one on the left with the little shared symbol is the original which you can't edit. |
| * The one on the right is your own personal copy. |
| <WRAP centeralign> |
| {{:howto:mobile_05.jpg?300}} {{:howto:mobile_06.jpg?300}} |
| </WRAP> |
| |
| * Every so often, you should delete your personal copy (or make a backup) and download a fresh copy so that you can have the latest database. |
| * Please do NOT edit the database in your personal copy since no-one else will benefit from your edits. Instead, email [[va7fi@rbox.me |Patrick, VA7FI]] the information that needs to be added / edited in the online version. |
| |
| |
| ====== Submit Updates ====== |
| To submit an update to the main online spreadsheet, use the [[https://docs.google.com/forms/d/e/1FAIpQLSc3Taz4Tvr-pABqXBsCBMtnhkxlly2jgiiDyzPxGa2qurb5Hw/viewform |Net Roll Call New Entry Submission Form]], which has a link in the spreadsheet: |
| {{ :howto:submitlink.png }} |
| |
| Which will take you to the form: |
| {{ :howto:submitform.png }} |
| |
| |
| |
| ====== Notes for Admins ====== |
| <WRAP prewrap> |
| |
===== Adding Names ===== | ===== Adding Names ===== |
To add a new name to the ''Data'' sheet, do <fc #ff0000>not Insert</fc> a line somewhere in the middle of the sheet to keep names sorted by callsigns. The problem with doing this is that Column A (which is hidden) and Column N contain important formulas and inserting a line leaves those cells blank for the new line. | To add a new name to the ''Local Data'' sheet: |
| - Edit the Google spreadsheet directly in the browser (<fc #ff0000>not your downloaded copy</fc>). That way everyone will benefit from your updates next time they download a fresh copy. |
| - Scroll down and use one of the empty lines below the current list. It doesn't have to be the very next line. For example, as of this writing, the last entry is in line 462, but you could use line 479. It really doesn't matter. All these empty lines at the bottom of the list have the formulas in Column A and N already pre-fielded. |
| |
| <WRAP center round alert 90%> |
| Do not Insert a line somewhere in the middle of the sheet to keep names sorted by callsigns. The problem with doing this is that Column A (which is hidden) and Column N contain important formulas and inserting a line leaves those cells blank for the new line. |
| </WRAP> |
| |
Instead, scroll down and use one of the empty lines below the current list. It doesn't have to be the very next line. For example, as of this writing, the last entry is in line 397, but you could use line 479. It really doesn't matter. All these empty lines at the bottom of the list have the formulas in Column A and N already pre-fielded. | |
{{ :howto:unsorted.png }} | {{ :howto:unsorted.png }} |
| |
All the entries are now sorted by callsign suffix: | All the entries are now sorted by callsign suffix: |
{{ :howto:sorted.png }} | {{ :howto:sorted.png }} |
| \\ |
| |
===== Deleting Names ===== | ===== Deleting Names ===== |
To delete a name, do <wrap warning>not Delete Row<wrap>. Instead delete the green cells containing the information. Careful not to delete anything in the red cells in columns A and N which contain the formulas. Then resort the list to send the empty line back to the bottom. | To delete a name, do <fc #ff0000>not Delete</fc> Row. Instead delete the green cells containing the information. Careful not to delete anything in the red cells in columns A and N which contain the formulas. Then resort the list to send the empty line back to the bottom. |
| \\ |
| |
===== Finding The File ===== | ===== Updating the "ISED Data" Tab ===== |
Currently, the file is not public. Members can access it from the [[memberscorner:home#google_drive|Member's section]], otherwise, contact [[va7fi@rbox.me |Patrick, VA7FI]] if you'd like access. | {{ :howto:ised_import.png?350}} |
| * Download [[http://www.ic.gc.ca/eic/site/025.nsf/eng/h_00004.html |Amateur call sign list (delimited TXT format)]]. |
| * Extract text file from zip file and change extension from: \\ ''.txt'' to ''.csv'' |
| * In Google Sheets, unhide and select the ''ISED Data'' tab. |
| * Go to ''File'' > ''Import'' > ''Upload'': |
| * ''Replace Current Sheet'' |
| * ''Custom:'' |
| * '';'' |
| |
| </WRAP> |
| |
===== Offline Use ===== | |
To use offline, you can download as a Microsoft Excel file or as an OpenDocument File: | |
{{ :howto:download.png }} | |
| |
I haven't tested the Excel version as I don't have Microsoft Office, but using the Free and Open Source office suite [[https://www.libreoffice.org/ |LibreOffice]] works. The only thing that doesn't (I'm not sure why) is checking for [[#birthdays_and_anniversaries |birthdays]]. So I do that on the online version before starting the net. | |