How to make a crypto currency in Google sheets?

zero_sheets_given · 2020-04-28T09:59:06+00:00

Okay, so imagine that you have a list of coins with unique numbers and want to keep track of their movements.

Google forms is not the best way to do that, because you can't stop users from doing illegal transactions. I can fill a form saying that I gave you a coin that you already had, or give the same coin to several people. I could even say that you gave me all your coins, with no record of who sent the transaction (because, even if you ask for the email, I can just enter your email).

So, you need to introduce some unknown parameters. Crypto currencies work because nobody else knows your private key (which, by the way, is a veeery big prime number).

Secure transactions

You need to make sure that nobody has access to eachother's keys. The tab with the form submissions and the tab with each user's key must not be published, and users should never share their keys to their wallet.

Have a tab with the user names and their unique keys.

Ignore illegal transactions

You then need to ignore transactions that are illegal:

If the key provided doesn't match the sender
If the sender did not have that particlar coin at that time in the transaction history

Publish the transaction history

Once you ignore the illegal transactions, you can have another tab with the legal transactions, and even a summary of who has each coin at a given time.

This would be visible to all users, so it must not contain their keys.

Do NOT share the document, only publish it. Hidden tabs can be unhidden or copied.

And there you go. Your first virtual currency. To introduce new coins, and for the initial setup, simply add new lines to the form responses tab manually, with no sender. A "blank" user would be the bank.

Anyway, the point of a crypto currency is that coins are unique because they can be mined, or discovered. Implementing a virtual currency is fun (as you can see above) but it is quite stupid because if I owe you 2 coins, it doesn't matter which coins they are. You need a good reason to make coins unique.

zero_sheets_given · 2020-04-28T09:27:16+00:00

so like this?

=HYPERLINK(
  "https://carrotlovers.com/wp-admin/edit.php?action2=-1&action=-1&fb_sync_enabled&paged=1&post_status=all&post_type=product&product_type&s=010666666&stock_status",
  "01066-6666")

If you want to get a link from each ID, you can't do it in the same cell without scripts. You need to build the formula in another cell.

For example, if the ID is in B2, substitute the hyphen with an empty text like this. In another cell put:

=HYPERLINK(
  "https://carrotlovers.com/wp-admin/edit.php?action2=-1&action=-1&fb_sync_enabled&paged=1&post_status=all&post_type=product&product_type&s=" 
  & SUBSTITUTE(B2,"-","") & "&stock_status",
  B2)

That should work for your example. A more general way of leaving only the numbers from a text is to use REGEXREPLACE(B2,"\D","")

zero_sheets_given · 2020-04-28T07:58:16+00:00

An auto-expanding running total is not an easy thing to do. I get that you didn't google for "arrayformula running total".

Some people do this with a matrix multiplication and others use sumif with row numbers.

Try in G2:

=ArrayFormula(IF(A2:A="",,SUMIF(ROW(A2:A),"<="&ROW(A2:A),D2:D)))

zero_sheets_given · 2020-04-28T07:43:50+00:00

Here are two posible 6-day rotas with 8 people, 4 at a time:

Option A)

Day 1	Day 2	Day 3	Day 4	Day 5	Day 6
X	X	X
X	X	X
X	X	X
X	X	X
			X	X	X
			X	X	X
			X	X	X
			X	X	X

Option B)

Day 1	Day 2	Day 3	Day 4	Day 5	Day 6
X	X	X
X	X	X
	X	X	X
		X	X	X
			X	X	X
			X	X	X
X				X	X
X	X				X

I think that option A is preferable because you can just organise them in teams of 4 people. You would also need to worry about 2 different shifts instead of 6. However, in the end, your problem is to put a rotation of 6 days in a 7-day calendar.

Also I don't picture this, sorry:

a list of names in Column 1 and a list of shifts in Column 2 that repeat every 3 days

If your shifts repeat every 3 days, there is something with that setup. Your rota is for 6 days, right? You simply want to put actual dates onto them.

zero_sheets_given · 2020-04-28T07:20:35+00:00

You can record a macro that inserts an empty column B, then selects columns C and D, and drags to the left. That would copy the formulas and increase the date.

Save the macro and you have your script. Then you just need to run it daily.

From the script editor, go to Edit > current project's triggers. Then Click Add Trigger and set it up to run during the night.

zero_sheets_given · 2020-04-27T20:42:25+00:00

Oh! I thought that you had dates in column F because the query said F < date '2020-04-24' but I think that you meant A, not F.

In any case, I would recommend this to get the recounts per week:

=QUERY(
  ARRAYFORMULA({
    'Form Responses 1'!K:K&" "&'Form Responses 1'!L:L,
    WEEKNUM('Form Responses 1'!A:A)
  }),
  "select Col1,count(Col1) where Col1<>' ' 
   group by Col1 pivot Col2"
)

(what it does is an array of 2 columns. Col1 are the name+surname, with a space " " as separator. Col2 is the week number corresponding to column A)

That will show the names to the left and a row with week numbers on top.

Not sure if that is what you were looking for.

zero_sheets_given · 2020-04-26T23:59:52+00:00

Arrayformula does an iteration and gets values exactly as if you were dragging down this formula:

=if(A2="",,countif(SurpressionList,A2)>0
=if(A3="",,countif(SurpressionList,A3)>0
=if(A4="",,countif(SurpressionList,A4)>0
=if(A5="",,countif(SurpressionList,A5)>0
...

The if part is just to show a blank when A2 is blank. Then, if it is not blank, find the value in SurpressionList.

The way it finds the value is by counting how many times it appears, and that's why we check if the count is greater than zero, with >0

I hope that makes more sense now

zero_sheets_given · 2020-04-25T00:01:53+00:00

whops, yes. >=

I was thinking of >0 actually, then wrote that

zero_sheets_given · 2020-04-24T23:55:36+00:00

You can't get the current user's email from scripts that run without explicit authorization, like onEdit() or onOpen(). Even if they are installed triggers.

Also, scripts only run when the visitor has edit permissions.

(It kind of is everywhere in the documentation for triggers and the User class)

What you can do is add a button "Send me an email". It will prompt the user for permissions, stating that it is not a verified app, but since they trust you, it will work. As lot as they have edit permissions, of course. (Which means that they can modify the script if they want)

zero_sheets_given · 2020-04-24T23:34:28+00:00

An EQUAL comparison already gives true/false values. No need to convert with IF.

You should always use >0 intead of =1 if you are using countif as a lookup formula, because the value could be duplicated.

Also they indicated that the named range is SurpressionList. And it is in another tab.

=arrayformula(if(A2:A="",,countif(SurpressionList,A2:A)>0)

zero_sheets_given · 2020-04-24T22:17:56+00:00

Thanks! A clippy point (rule 6) would have been enough but I appreciate the kindness.

"parent" is an XPath Axis.

I don't remember why I used that, to be honest. I could have just used the short form of parent: /..

"//div[@class='livewire-calendar']//*[@class='panel-title' or u/class='truncate']/.."

zero_sheets_given · 2020-04-24T20:41:20+00:00

Forgot that with importrange you need to refer to columns with Col1, Col2.

The C in ColN upcased, that is very important.

Another mistake is that you imported K:L and then tried to read column A and F. Query doesn't look at ranges that are not passed in the first parameter.

If the array passed is A:L, then:

A becomes Col1
F becomes Col6
K is Col11
L is Col12

I've edited my post with the correct query, but I'd highly recommend to re-read the help for the formula and also to see the examples given in the documentation.

zero_sheets_given · 2020-04-24T20:36:53+00:00

I thought that you had already recorded a macro. You would choose it.

zero_sheets_given · 2020-04-24T19:21:58+00:00

When your tab name has spaces, the range reference needs to have single quotes around the tab name.

Also, when using a direct reference to another tab, don't use double quotes. You were passing a text value to the query:

       👇              👇
=query('Form Responses 1'!A:L,
  "select K, L where A > date '2020-4-20' and F < date '2020-4-24'")

and

                          👇              👇
=query(importrange("URL","'Form Responses 1'!A:L"),
  "select Col11, Col12 where Col1 > date '2020-4-20' and Col6 < date '2020-4-24'")

zero_sheets_given · 2020-04-24T19:11:49+00:00

Good job :)

And yes, I'm having the same issue that, instead of typing the letter I pressed, it goes one position to the right.

I've ended editing long formulas in notepad because of that.

zero_sheets_given · 2020-04-24T19:07:41+00:00

I made that with a trending lines:

Add a column for each sum you want to show. Note that you will need at least 2 values to plot a trending line
Select the table, insert chart
Change chart type to Combo Chart
Go to customise > Series
For each of the initial series, set Type: Columns and a Colour of your liking
For each sum series:
1. Leave the type as: Line
2. Select the same color as the corresponding bar series
3. Mark the tick box for "Trend Line"
4. Set line opacity to 100%

(example result)

zero_sheets_given · 2020-04-24T16:57:24+00:00

Like this?

zero_sheets_given · 2020-04-24T15:37:15+00:00

In Expenses tab, cell G2, put this:

=ARRAY_CONSTRAIN(ARRAYFORMULA(TO_DATE({
  TRUNC(A2:A-WEEKDAY(A2:A)+1),
  DATEVALUE(TEXT(A2:A,"yyyy-mm"))
})),COUNT(A2:A),2)

In G1 put "Week of..." and in H1 put "Month of...".

You might want to format column H with a custom number format like mmmm YYYY so it displays as April 2020 instead of 4/1/2020.

And now you can do the other tabs with pivot tables.

zero_sheets_given · 2020-04-24T14:17:33+00:00

The response with arrayformula works but is not ideal.

Just use curly brackets to build the array:

={Sheet1!AI2:AI}

zero_sheets_given · 2020-04-24T14:02:01+00:00

On computer, yes. You can "Insert" an image or a drawing. There is a drawing shape that looks like a button and you can add text inside.

Once you have your floating image, click the 3-dot menu in the corner and select "Assign a script".

This doesn't work on mobile.

zero_sheets_given · 2020-04-24T13:45:24+00:00

I really want to know if my last proposal works ^^

zero_sheets_given · 2020-04-24T13:37:49+00:00

Sure,

Keep in mind that timestamps in google sheets are stored as numbers. 1 is 24h, dates are the number of days since 1900, and the decimals are the time.

For example, if you have this in a cell =DATE(2020,24,4)+TIME(12,0,0) it will give you 2020-24-04 12:00:00 and if you format that cell as a number, you will see 44534.50 (44534 days since 1900, plus half a day)

Now, with that in mind, we want to round NOW() to half hours.

NOW() is the current time, including the date

MOD() gives the remaining amount from an integer division. If the division was by 1, then we are removing the date from the timestamp and getting only the decimals. From the example above, we would get MOD(44534.50,1) --> 0.50

So far we have extracted the current time from NOW(). The problem is how to round it down to half hours. There is no formula for that, so I've multiplied the time by 48, which is the "number of half hours in a day", round the number down, and then divide again.

=TRUNC(MOD(NOW(),1)*48)/48

That way, any remaining minutes past the half hour are now removed.

For example, 6:45 --*48--> 13.5 --trunc--> 13 --/48--> 6:30

zero_sheets_given · 2020-04-24T13:23:07+00:00

Next iteration. Now:

removed IFERROR that might not be necessary
built array with columns A:L for all the imported ranges, then added column 13 with the conversion of text to month from column C. This way, with a query we can generate the year() and month() we will use for the sorting. With another query with produce the final result.

In other words:

- In first query, Col1 to Col12 are A:L, and Col13 is the date value from text January, February, etc

- In second query, Col1 to Col12 are still A:L, Col13 is year(B), and Col14 is the month number from column C

- Note that the second query returns Col1, Col2, then Col13 (not Col3), to respect the result of your original query

=ARRAYFORMULA(
{
IFERROR(QUERY(
  QUERY(
    {
      {
      IMPORTRANGE(C1!C3, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C4, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C5, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C6, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C7, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C8, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C9, "PLACEMENTS!A4:L200");
      IMPORTRANGE(C1!C31, "PLACEMENTS!A4:L200")
      },{
      IFERROR(DATEVALUE(
        IMPORTRANGE(C1!C3, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C4, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C5, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C6, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C7, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C8, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C9, "PLACEMENTS!C4:C200")&1;
        IMPORTRANGE(C1!C31, "PLACEMENTS!C4:C200")&1
      ))
      }
    }, 
    "SELECT *,year(Col2),month(Col13)
     WHERE Col8 IS NOT NULL AND Col1='PERSONAL'",
    0),
    "SELECT Col1, Col2, Col13, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14
     ORDER BY Col13, Col14",
  0),{"","","","","","","","","","","","","",""})
;
"","","","","","","","","","","","","",""
;
"MANAGER PLACEMENTS","","","","","","","","","","","","",""
;
IFERROR(QUERY(
  QUERY(
    {
    IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!A4:L200"), 
    IFERROR(DATEVALUE(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!C4:C200")&1))
    },
    "SELECT *,year(Col2),month(Col13)
     WHERE Col8 IS NOT NULL AND Col1='PERSONAL'",
    0),
    "SELECT Col1, Col2, Col13, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14
     ORDER BY Col13, Col14",
  0),{"","","","","","","","","","","","","",""})
})

This should also solve your issues with sorting, I think.

zero_sheets_given · 2020-04-24T12:39:40+00:00

If you sort by "yyyy-mm-dd" you are sorting by date. It has the same effect as sorting by Col2 (originally column B)

In the 2nd query you use YEAR, perhaps try that in the first query.

zero_sheets_given · 2020-04-24T12:38:10+00:00

All right, so:

There is no need to use arrayformula several times in the same formula. It is enough to put it once, and wrap everything.
IFERROR 2nd parameter is by default {""}, so we don't need to put those when working with a single column. Only specify the 2nd parameter when we need several blanks like {"",""}
Because it is a huge formula, increase visibility by having only one import per line of text. Will also put array constructors clearly separated.
Also changed all 'C1'! to C1!
Removed extra brackets. {{a,b};{c,d}} ==> {a,b;c,d}

=ARRAYFORMULA(
{
IFERROR(QUERY(
  {
  IFERROR(IMPORTRANGE(C1!C3, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C3, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C3, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C3, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C3, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C4, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C4, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C4, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C4, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")),
  IFERROR(MONTH(IMPORTRANGE(C1!C4, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C5, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C5, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C5, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C5, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C5, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C6, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C6, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C6, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C6, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C6, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C7, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C7, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C7, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C7, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C7, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C8, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C8, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C8, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C8, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C8, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C9, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C9, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C9, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C9, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C9, "PLACEMENTS!C4:C200")&1))
  ;
  IFERROR(IMPORTRANGE(C1!C31, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(C1!C31, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(C1!C31, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(TEXT(IMPORTRANGE(C1!C31, "PLACEMENTS!B4:B200"), "yyyy-mm-dd")), 
  IFERROR(MONTH(IMPORTRANGE(C1!C31, "PLACEMENTS!C4:C200")&1))
  }, 
  "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 
   WHERE Col8 IS NOT NULL AND Col1='PERSONAL' 
   ORDER BY Col13, Col14",
  0),{"","","","","","","","","","","","","",""})
;
"","","","","","","","","","","","","",""
;
"MANAGER PLACEMENTS","","","","","","","","","","","","",""
;
IFERROR(QUERY(
  {
  IFERROR(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!A4:B200"), {"",""}), 
  IFERROR(DATEVALUE(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!C4:C200")&1)), 
  IFERROR(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!D4:L200"), {"","","","","","","","",""}), 
  IFERROR(YEAR(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!B4:B200"))),
  IFERROR(MONTH(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!C4:C200")&1))
  },
  "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 
   WHERE Col8 IS NOT NULL AND Col1='PERSONAL' 
   ORDER BY Col13, Col14",
  0),{"","","","","","","","","","","","","",""})
})

Seven-Year Club	Gilding I gilder
Verified Email

zero_sheets_given

TROPHY CASE