Sun Coast Amateur
Radio Club Society

User Tools

Site Tools

You are here: home » howto » netspreadsheet
howto:netspreadsheet

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
howto:netspreadsheet [2020/09/29 10:34] – va7fihowto:netspreadsheet [2021/06/27 08:53] – [Notes for Admins] va7fi
Line 2: Line 2:
  
 <WRAP center round alert> <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.+The Google Spreadsheet can only be edited by the owners.  Everyone else needs to download the Excel or LibreOffice version for offline use.
 </WRAP> </WRAP>
  
Line 12: Line 12:
 Currently, the file is not public, contact [[va7fi@rbox.me |Patrick, VA7FI]] if you'd like access. 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:+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  }} {{  :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 anything that needs to be updated, please email [[va7fi@rbox.me |Patrick, VA7FI]].  That way, other users will benefit from your report. +
-  * Download a fresh copy periodically so as to always have the latest database.+
  
  
Line 25: Line 23:
 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 }}
  
-  * ''Local Data'' contains the actual information about a few hundred hams in the Vancouver Island area. +|<100% 200px - >| 
-  * ''LogSheet (Local + ISED)'' is the "search / log" page used during the net. +^Local Data |contains the actual information about a few hundred hams in the Vancouver Island area.| 
-  * ''LogSheet (Local)'' is a lighter version of the previous tab (for slower machines) that doesn't use ISED database. +^LogSheet (Local + ISED) |is the "search / log" page used during the net.| 
-  * ''LogSheet (ISED)'' is a version that only searches the ISED database.  This could be useful for cross Canada nets. +^LogSheet (Local) |is a lighter version of the previous tab (for slower machines) that doesn't use ISED database.| 
-  * ''Instructions'' links to this page. +^LogSheet (ISED) |is a version that only searches the ISED database.  This could be useful for cross Canada nets.| 
-  * ''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 Sept 21, 2020.+^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%> <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> </WRAP>
  
Line 46: Line 47:
 |Column A           |<fc #ff0000>✘</fc> |Is hidden and contains formulas that the other tab needs. | |Column A           |<fc #ff0000>✘</fc> |Is hidden and contains formulas that the other tab needs. |
 |Columns B -- M     |✔ |Contains information about ham operators. | |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. | |Columns N -- P     |<fc #ff0000>✘</fc> |Contains formulas and should NOT be edited. |
  
Line 86: Line 87:
   * 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''.   * 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''.
   * 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''.   * 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''.
-  * When ''wek'' is entered, it's not found in either databases so it returns ''Not Found''.  If you know that this is a valid callsign (maybe a new ham), please email [[va7fi@rbox.me |Patrick]] so he can update the ''Local Data''.+  * When ''wek'' is entered, it's not found in either databases so it returns ''Not Found''.
  
 Here are a few more details: Here are a few more details:
Line 95: Line 96:
   * The top shows the number of Birthdays and Anniversaries, which are also flagged in column O.   * 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 131: Line 136:
   * 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.   * 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.   * 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 ====== ====== Notes for Admins ======
-<hidden> 
 <WRAP prewrap> <WRAP prewrap>
  
Line 159: Line 172:
 \\ \\
  
-===== Updating ISED Data Tab ===== +===== Updating the "ISED Data" Tab ===== 
-{{ :howto:ised_import.png}}+{{ :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)]].   * 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''+  * Extract ''amateur_delim.txt''
   * In Google Sheets, unhide and select the ''ISED Data'' tab.   * In Google Sheets, unhide and select the ''ISED Data'' tab.
   * Go to ''File'' > ''Import'' > ''Upload'':   * Go to ''File'' > ''Import'' > ''Upload'':
     * ''Replace Current Sheet''     * ''Replace Current Sheet''
-    * ''Custom:  ;''+    * ''Custom'' 
 +    * '';''
  
 </WRAP> </WRAP>
-</hidden>+
  
howto/netspreadsheet.txt · Last modified: 2021/08/09 09:28 by va7fi