Skip to main content
deleted 1 character in body
Source Link

Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:

When Google Forms fills out a spreadsheet it empties the linerow it fills out, but it leaves intact all the other lines. This means that it's possible to put something on the very first row without it ever getting deleted.

Additionally some functions (functions that return arrays) are able to return multiple cells. You can see this if you enter the following into an empty cell for example:

={{1,1};{1,1}}

This thus allows us to create a function that will fill out the ID without getting it erased. The basic idea is:

=arrayformula(
  if(
    row(A1:A) = 1,
    "ID",
    if(
      not(isblank(A1:A)),
      "ID GENERATING CODE",
      ""
    )
  )
)

The simplest code to replace the "ID GENERATING CODE" with is row(A1:A)-1, but this will break if you delete a row (not an issue in my case, but can be an issue in yours). If it is an issue I would suggest adding a custom hash function and filling the id as hash(timestamp & name) or something along those lines.

Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:

When Google Forms fills out a spreadsheet it empties the line it fills out, but it leaves intact all the other lines. This means that it's possible to put something on the very first row without it ever getting deleted.

Additionally some functions (functions that return arrays) are able to return multiple cells. You can see this if you enter the following into an empty cell for example:

={{1,1};{1,1}}

This thus allows us to create a function that will fill out the ID without getting it erased. The basic idea is:

=arrayformula(
  if(
    row(A1:A) = 1,
    "ID",
    if(
      not(isblank(A1:A)),
      "ID GENERATING CODE",
      ""
    )
  )
)

The simplest code to replace the "ID GENERATING CODE" with is row(A1:A)-1, but this will break if you delete a row (not an issue in my case, but can be an issue in yours). If it is an issue I would suggest adding a custom hash function and filling the id as hash(timestamp & name) or something along those lines.

Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:

When Google Forms fills out a spreadsheet it empties the row it fills out, but it leaves intact all the other lines. This means that it's possible to put something on the very first row without it ever getting deleted.

Additionally some functions (functions that return arrays) are able to return multiple cells. You can see this if you enter the following into an empty cell for example:

={{1,1};{1,1}}

This thus allows us to create a function that will fill out the ID without getting it erased. The basic idea is:

=arrayformula(
  if(
    row(A1:A) = 1,
    "ID",
    if(
      not(isblank(A1:A)),
      "ID GENERATING CODE",
      ""
    )
  )
)

The simplest code to replace the "ID GENERATING CODE" with is row(A1:A)-1, but this will break if you delete a row (not an issue in my case, but can be an issue in yours). If it is an issue I would suggest adding a custom hash function and filling the id as hash(timestamp & name) or something along those lines.

Source Link

Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:

When Google Forms fills out a spreadsheet it empties the line it fills out, but it leaves intact all the other lines. This means that it's possible to put something on the very first row without it ever getting deleted.

Additionally some functions (functions that return arrays) are able to return multiple cells. You can see this if you enter the following into an empty cell for example:

={{1,1};{1,1}}

This thus allows us to create a function that will fill out the ID without getting it erased. The basic idea is:

=arrayformula(
  if(
    row(A1:A) = 1,
    "ID",
    if(
      not(isblank(A1:A)),
      "ID GENERATING CODE",
      ""
    )
  )
)

The simplest code to replace the "ID GENERATING CODE" with is row(A1:A)-1, but this will break if you delete a row (not an issue in my case, but can be an issue in yours). If it is an issue I would suggest adding a custom hash function and filling the id as hash(timestamp & name) or something along those lines.