Home‎ > ‎Member Spreadsheets‎ > ‎

How to make User Spreadsheets

This guide is for the mods of the project, to ensure we all use the same method when creating a Spreadsheet (and to teach new mods how to do it :-P).

A Spreadsheet for a New Member


When a new member signs up, they generally get a welcoming PM with access to the New Members Spreadsheet. There they can register their first dragons right away and decide whether they'd like to stay with us.
If the spreadsheet and/or their thread activity after 2 months shows that they're still active, they get their own spreadsheet. The dragons they have registered up to that point are transferred into their own spreadsheet and then deleted from the New Members Spreadsheet.

Creating an Empty Spreadsheet

Creating the Registration Form

Everything starts with the creation of the Registration Form, which is mainly a copy of an existing Registration Form.

Please make sure you use a Registration Form as basis that is up to date on the breeds we accept (e.g. your own, if you keep it updated; else, look around)!

Sign into the group's google account and go to the Registration Form you want to use as basis (if you're not using your own, go to https://drive.google.com to find the one you need). 

From there, click File => Make a copy (in the menu, upper left corner). Then change the name to represent the new member's name, then click "OK":

Wait a few seconds for the new Form to be created. Then change the user name (and gender pronoun, if gender known) in the first 2 lines accordingly:
Then click "View Responses" in the lower menu line, which should trigger the creation of the Spreadsheet:

Adjusting the new spreadsheet

When the new Spreadsheet has opened, click File => Rename (in the upper left corner), then change the Spreadsheet name according to the new member's name and click "Ok":
Now we have a spreadsheet with one tab, the one where the dragons registered via the Registration Form will appear.
Click on the little arrow head right next to the tab's name ("Form Responses") on the bottom of the tab, and choose "Rename...", then change the tab name to "Dragons":

After that, you can adjust the column widths of the individual columns, if you like (it doesn't matter much; the user will probably adjust them again to their liking).
Then click on column E ("Element") and then in the menu, choose Format => Conditional Formatting, to color-highlight the different Elements.
In the field next to "Text contains" you enter the name of an Element, then you choose the formatting like this:
  •     Earth => Text: White => Background: Brown
  •     Fire => Background: Red
  •     Life => Background: Light Green
  •     Water => Background: Dark Blue
  •     Wind => Background: Cyan
Click "+Add another rule" to get a new line for the next element.
The order of the elements is unimportant. When choosing the colors, choose those from the uppermost row.
When you have them all, click "Save rules":

Then we can add the other tabs.

To add a new tab, click the small "+" symbol on the left bottom, next to the existing tabs.

Create and rename the following tabs, additionally to the "Dragons" tab you already have:

  • DragonsPerBreed
  • DragonsPerCategory
  • Milestones
  • Charts
(You can always use your own spreadsheet as guide for these. I do. You'll need a reference sheet to copy over stuff, anyway.)

The DragonsPerBreed Tab:
Go to the same tab in your reference spreadsheet (e.g. your own) and copy its whole content via CTRL + A, then paste it into the upper left cell of the new spreadsheet.
Select the A column ("Element") in the new spreadsheet and use Conditional Formatting (just like in the "Dragons" tab) to highlight the elements.
Adjust the widths of the columns to their content.
Make sure all the numbers in the "Milestone Claimed" column say "0" and all the numbers in "Next Milestone" say "15".
Then use your mouse to drag down the little bar right above the "1" labelling the first row to rest between the "1" and the "2" below it, making the first row the header:

The DragonsPerCategory Tab:
Once again, copy the whole content of the same tab from your reference sheet into the new sheet. (You may need to press CTRL+A twice in a row to copy everything.)
Change the width of the G column to be 3 columns wide. After that, all rows should have a normal height.
In the A column ("Member"), replace all the names with the new member's name:
Again, make sure all the numbers in the "Milestone Claimed" column say "0" and all the numbers in "Next Milestone" say "15".
Like in the previous tab, drag down the little bar to make the first row into a header.
Also, once again adjust the column widths to their content.
The DragonsPerCategoryTab:OnceYour tab should look like this now:

The Milestones Tab:
Copy the FIRST ROW of your reference sheet's Milestones Tab into the new sheet.
Like in the previous tab, drag down the little bar to make the first row into a header.
That's it. This tab remains empty except for the header (which is only a suggestion) until the owner uses it.

The Charts Tab:
Once again, copy the whole content of the same tab from your reference sheet into the new sheet. (You may need to press CTRL+A twice in a row to copy everything.)

Adjust the width of columns A and B to be 3 column wide, each.

Making the Spreadsheet ready for transfer

Adding the script that reformats the dates:
Go back to the first tab ("Dragons"). In the menu, choose Tools => Script Editor.
Replace the code with the following:
//Function to filter unwanted " or ' chars from date entries
function reformatDate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dates = sheet.getRange(2, 3, sheet.getLastRow(), 1).getValues();
  newdates = []
  for(var i in dates){
    var mydate = dates[i][0];
    try
      {
      var newdate = mydate.replace(/'/g,'').replace(/"/g,'');
      }
    catch(err)
      {
      var newdate = mydate
      }
     newdates.push([newdate]);
  }
  sheet.getRange(2, 3, sheet.getLastRow(), 1).setValues(newdates)
}
Then click Resources => Current project's triggers.
In the promt, rename the script to "ReformatDate", then click "OK":
Then click on "No triggers set up. Click here to add one now.":
Then set the triggering event to "On form submit" and click "Save":
Accept the Authorisation Request popping up.
When you're done, you can close the tab with the script.

Testing the Form:
Go to the Registration Form you made and enter some dummy test information about a hypothetical dragon.
Check that it appears in the "Dragons" tab of your new sheet, as expected.
(If it doesn't appear right away, try "Edit Form" => "View Responses", which will open a new view of the spreadsheet. The dummy dragon should be in there now.

Adding the already registered dragons:
In a new tab, open the "Elementals by New Members" spreadsheet.
In the header row, there should be little arrowheads next to the column names, like in this screenshot:

If not, click Data => Filter in the menu.

Click the little arrow head next to "Forum Name", then in the filter, make sure only the name of the new member you're working on is checked, the rest unchecked, then click "OK":
Now you should only see the dragons registered by your new member. Look up how many dragons there are.
Go to the "Dragons" tab of your new sheet. Create enough rows ABOVE the one with the dummy dragon to have space for all the already registered dragons. (You do that by selecting the dummy row (row 2) plus as many rows as you need to insert, then right-clicking on any of the numbers next to the selected rows, and clicking "Insert XX rows", with XX representing the number of rows.) You don't have to add them all at once. Just make sure you have enough empty rows before inserting the data. When in doubt, rather insert a few more rows than necessary. It's easy to delete them afterwards.
Now go back to the "New Members Spreadsheet", and for all the rows with dragons registered by your new member, select all the content of the columns C to L (= everything except the first 2 columns) and copy it (CTRL+A).
Then go back to your new sheet's "Dragons" tabs, select the cell B2 (the one directly under "Name") and insert the copied data.
Now you should have all the already registered dragons of your new member in their new spreadsheet (without a timestamp).
If there are empty rows between the copied data and the dummy row, delete the empty rows.

The copied-over dragons probably lost the Elemental Highlighting colors. Selecting the column E and choosing Format => Conditional Formatting => Save rules should correct that.
Just to make sure newly registered dragons now appear BELOW the already registered dragons, register another dummy dragon via the Registration Form.
If that test results as expected, you can delete all dummy rows.

Once you're done, remember to delete all the copied-over dragons from the "New Members Spreadsheet" and set the Name filter back to "Select all", to avoid confusing other new members. ;-)

Handing over Spreadsheet and Form:
When the spreadsheet is done, click on the blue "Share" button in the upper right corner, and select "Who has access: Public on the web". Then click "Done".
Add the link to the new Spreadsheet to the "Members" tab of the Mastersheet.

Then send the new member a "Here's your spreadsheet" PM, with links to their Spreadsheet and their Registration Form.
Once they send you their email address, you can grant them editing access via the same "Share" button of the spreadsheet, and the "Send Form => Add collaborators" (also a blue button, in the upper right corner of the "Edit Form" view).


A Spreadsheet for a Returning Member

When an inactive member, who went on hiatus before individual spreadsheets were implemented, becomes active again, it means they don't have their own spreadsheet yet and need to get one. It also means the dragons they already registered before going inactive are in the Inactive Members Spreadsheet and need to be transferred from there.

Basically, you create the spreadsheet just like you would for a new member.
But instead of copying over dragons from the "New Members Spreadsheet", you copy them from the "Inactive Members Spreadhsheet".
Everything else should be pretty much the same.
Comments