Kurtworks said:
I would like Google Docs to generate a unique ID in the first column of my spreadsheet as soon as put data in that row. I would like the unique ID to take a number from the F column, the first letter of the first words from column's B and C and the timestamp the data was entered.
The Final unique ID would look like this 9KA1010201105034
9 being the number from column F
KA being the first letter of the first word in columns B and C
1010201105034 being the timestamp the data was entered (10/10/2011 05:34)
Is this possible to do? I have not clue how to script this so it there already a script out there that I could modify to do this? How would I do that?
---------------------------------------------------------------------------
In the following illustration I have created the UniqueID based on Kurtworks specification in column G just to the right of the ResponseTable in columns A:F
formula in cell G1 is ...
=ArrayFormula(if(row(G1:G)=1,"UniqueID",F1:F&B1:B&C1:C&substitute(text(A1:A,"mmddyyyyHH:mm"),":","")))
---------------------------------------------------------------------------
DarrenMWinter commented ...
I've been trying to adapt your formula here but am having some trouble with the time. I've adapted your formula to read:
=ArrayFormula(if(row(A1:A)=1,"UniqueID",C1:C&E1:E&substitute(text(F1:F,"mmdd-hh:mm"),":","")))
Where A is the row that the number appears in; and C and E are the reviewer's initials and date the comment was made.
The first result give is "m2dmw1024-1200". This is all right except for the time part. I don't understand why it reads 1200 for the time.
What I'd like to do is record something unique at the end of the ID number, like the time as hhmmss that the comment was entered - or even, as I have before in different software, make the unique part after the hyphen to read the number of seconds after midnight that the comment was created.
---------------------------------------------------------------------------
DarrenMWinter:
in regard to your first comment, what is the time in the TimeStamp column? ... keep in mind that
=text(value,"hh:mm") ... gives the hours in 12 hour clock format
=text(value,"HH:mm") ... gives the hours in 24 hour clock format
Hi
ReplyDeleteI've been trying to adapt your formula here but am having some trouble with the time. I've adapted your formula to read:
=ArrayFormula(if(row(A1:A)=1,"UniqueID",C1:C&E1:E&substitute(text(F1:F,"mmdd-hh:mm"),":","")))
Where A is the row that the number appears in; and C and E are the reviewer's initials and date the comment was made.
The first result give is "m2dmw1024-1200". This is all right except for the time part. I don't understand why it reads 1200 for the time.
What I'd like to do is record something unique at the end of the ID number, like the time as hhmmss that the comment was entered - or even, as I have before in different software, make the unique part after the hyphen to read the number of seconds after midnight that the comment was created.
I'm a bit of a newbie with Google docs and I am finding my way slowly. Your comments on the forums and on this site have been very useful so far, so thanks for that!
Hi DarrenMWinter:
ReplyDeletein regard to your first comment, what is the time in the TimeStamp column? ... keep in mind that
=text(value,"hh:mm") ... gives the hours in 12 hour clock format
=text(value,"HH:mm") ... gives the hours in 24 hour clock format
so try ...
=ArrayFormula(if(row(A1:A)=1,"UniqueID",C1:C&E1:E&substitute(text(F1:F,"mmdd-HH:mm"),":","")))
and let me know if this resolves the problem.