Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision |
howto:netspreadsheet [2020/09/29 10:38] – [Basic Idea] va7fi | howto:netspreadsheet [2021/06/27 08:53] – [Notes for Admins] va7fi |
---|
| |
<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> |
| |
{{ :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. | |
| |
| |
| |
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. | |
* ''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. | |
* ''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. | |
| |
|<100% 200px - >| | |<100% 200px - >| |
^LogSheet (ISED) |is a version that only searches the ISED database. This could be useful for cross Canada nets.| | ^LogSheet (ISED) |is a version that only searches the ISED database. This could be useful for cross Canada nets.| |
^Instructions |links to this page.| | ^Instructions |links to this page.| |
^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.| | ^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> |
| |
|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. | |
| |
* 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: |
* 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. |
| |
| |
* 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> |
| |
\\ | \\ |
| |
===== 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> | |
| |