Differences

This shows you the differences between two versions of the page.

--- howto:netspreadsheet [2020/09/07 15:42] – [Android Tablets/Phones] va7fi
+++ howto:netspreadsheet [2021/08/09 09:28] (current) – [LibreOffice or Excel] va7fi
@@ Line 1: / Line 1: @@
+====== Net Roll Call Spreadsheet ======
-====== Net Logging Spreadsheet ======
 <WRAP center round alert>
-The spreadsheet cannot be used directly from the browser anymore. To use it, download the Excel or LibreOffice version for offline use.
+The Google Spreadsheet can only be edited by the owners.  Everyone else needs to download the Excel or LibreOffice version for offline use.
 </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.
+===== Quick Demo =====
+For those who don't like to read instructions...
+{{  netrollcall.gif  }}
 ===== 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 as an OpenDocument File:
+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  }}
-A few things to keep in mind:
+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.
-  * If you notice an error or omission, please email [[va7fi@rbox.me |Patrick, VA7FI]].  That way, other users will benefit from your updates.
-  * Discard and download a fresh copy every time you need it so as to always have the latest database.
@@ Line 22: / Line 23: @@
 ===== Basic Idea =====
-  * The spreadsheet has a few tabs that can be selected at the bottom of the page: {{ :howto:tabs.png }}
+The spreadsheet has a few tabs that can be selected at the bottom of the page: {{ :howto:tabs.png }}
-    * ''Data'' contains the actual information about a few hundreds of hams.
-    * ''LiveLogSheet'' is the "search / log" page used during the net.
+|<100% 200px - >|
-    * ''Instructions'' links to this page and lists a few changes I've made.
+^Local Data |contains the actual information about a few hundred hams in the Vancouver Island area.|
-    * ''ISED'' 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.
+^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.|
 <WRAP center round important 80%>
-The most important thing 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>.
+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 Data Tab =====
+===== The Local Data Tab =====
 {{  :howto:datatab.png  }}
+{{:howto:localdatatab.png}}
-The ''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":
+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":
 |<100% 11em >|
 ^Column(s)          ^Edit ^Details ^
 |Column A           |<fc #ff0000>✘</fc> |Is hidden and contains formulas that the other tab needs. |
 |Columns B -- M     |✔ |Contains information about ham operators. |
-|Columns E, G, I, K |✔ |Are dates that should be entered as so '' 'JAN 1''.((Dates must be formatted with three letters and be preceded by an apostrophe (') to force the proper formatting.)) |
+|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. |
@@ Line 60: / Line 68: @@
-===== 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  }}
-The ''LiveLogSheet'' tab is really the meat of this spreadsheet.  During the net, all you need to do is type the suffix in Column A (**not** case sensitive).
+  * 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.
-  * Column B can be left blank unless there are two callsigns with the same suffix (ex. VA7JH and VE7JH), or if the suffix is not in the ''Data'' tab at all (ex. "AAA").  If that's the case, Column C will ask you to add the prefix in Column B.
+  * 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''.
-  * Once Column C has the full callsign, it'll look up the information in the ''Data'' tab, and if not found there, it'll look in the ''ISED'' tab.
+  * 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''.
-  * Information from the ''ISED'' tab will have "(ISED)" written in the cells (ex. VE7AAA)
+  * When ''wek'' is entered, it's not found in either databases so it returns ''Not Found''.
-  * If the callsign doesn't exist (ex. VE7WEK), the cells will be left blank.
+Here are a few more details:
   * The cells in orange <fc #ff0000>should NOT be edited or deleted.</fc>
-  * Cell D1 shows the total number of checkins.
+  * Information from the ''ISED'' tab will have "(ISED)" written in the cells (ex. VE7AAA)
-  * The top shows the number of Birthdays and Anniversaries.
+  * 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>)
+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>).
-Note that I made up Richard's (VA7AA) birthday for the sake of illustration.
+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.
@@ Line 98: / Line 128: @@
   * You are now using your copy, which you can edit.
-  * At the bottom, select ''LiveLogSheet (lite)'', which doesn't query the ''ISED'' tab and is quicker for mobile devices.
+  * 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.
@@ Line 110: / Line 140: @@
-/*
+====== 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 =====
-To add a new name to the ''Data'' sheet:
+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.
@@ Line 127: / Line 168: @@
 All the entries are now sorted by callsign suffix:
 {{  :howto:sorted.png  }}
+\\
 ===== Deleting Names =====
 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.
+\\
+===== Updating the "ISED Data" Tab =====
+{{ :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 ''amateur_delim.txt''
+  * In Google Sheets, unhide and select the ''ISED Data'' tab.
+  * Go to ''File'' > ''Import'' > ''Upload'':
+    * ''Replace Current Sheet''
+    * ''Custom''
+    * '';''
+</WRAP>
-*/