Automating the Digital Signature of an Excel Spreadsheet

In product development, we need to have a clear understanding of the pain to be resolved.

TLDR: too long; didn’t read

I made an integration between a spreadsheet Google Spreadsheet with the digital signature platform Autentique using Graphql API and AppScript.

In my church, tithes and offerings must be counted and three people must sign: 2 deacons and the treasurer, for possible audits. The process was done as follows:

1 - A deacon opened the envelopes and counted the amounts

2 - Another deacon put the value and classification in an excel sheet.

3 - The worksheet was printed and signed by the two deacons.

4 - The treasurer came and checked the total amount and signed.

Pain:

• The process generated a problem at the time of printing, the printer failed;
• The file was saved offline on the church's computer, which made remote access difficult during the week;
• It had to be erased in case of errors... And it wasn't signed again due to lack of time;

Solution:

The proposal was to use an online spreadsheet (Google Spreadsheet) integrated with a digital signature platform ([Autentique](https: //www.autentique.com.br/)).

For this, it was necessary to verify if Autentique had an API for creating documents for signature:

mutation CreateDocumentMutation(
  $document: DocumentInput!, # Definição das variáveis $document,
  $signers: [SignerInput!]!, # $signers e $file, com seus respectivos
  $file: Upload!             # tipos. (Os "!" indicam que são
) {                          # parâmetros obrigatórios)
  createDocument(
document: $document, # Passa para os parâmetros da mutation o 
signers: $signers,   # valor das variáveis.
file: $file          #
  ) {
id
name
refusable
sortable
created_at
signatures {
  public_id
  name
  email
  created_at
  action { name }
  link { short_link }
  user { id name email }
}
  }
}

Fonte

BINGO!

Now the challenge was to convert the spreadsheet to a PDF document and send it to Autentique via this API.

Converting a spreadsheet to PDF

Google itself provides a parameterized spreadsheet export API 🤩.

Check the code: Embedded content: https://gist.github.com/kibolho/3be15d77d2a74bc3033743c36f6cb3c0.js

Upload PDF to Graphql API

"File uploading in GraphQL is not that common. Perhaps this is because there is no official support and the GraphQL documentation is strangely silent about file uploads." Source

Therefore, I reverse-engineered the PDF to the format desired by the API. And I found that it was by wrapping the request in form-data with curly braces:

map: 0: ["variables.file"].

------WebKitFormBoundary8vWNyCgaKSptloVN\r\nContent-Disposition: form-data; name="operations"\r\n\r\n{"query":"mutation CreateDocumentMutation(\n $document: DocumentInput\u0021,\n $signers: [SignerInput\u0021]\u0021,\n $file: Upload\u0021\n) {\n createDocument(\n \n document: $document,\n signers: $signers,\n file: $file\n ) {\n id\n name\n refusable\n sortable\n created_at\n signatures {\n public_id\n name\n email\n created_at\n action { name }\n link { short_link }\n user { id name email }\n }\n }\n}","variables":{"document":{"name":"Contrato de teste"},"signers":[{"email":"abilio_azevedo@hotmail.com","action":"SIGN"},{"email":"abilio10@gmail.com","action":"SIGN"}],"file":null},"operationName":null}\r\n

------WebKitFormBoundary8vWNyCgaKSptloVN\r\nContent-Disposition: form-data; name="map"\r\n\r\n{"0":["variables.file"]}\r\n

------WebKitFormBoundary8vWNyCgaKSptloVN\r\nContent-Disposition: form-data; name="0"; filename="boleto.pdf"\r\nContent-Type: application/pdf\r\n\r\n\r\n------WebKitFormBoundary8vWNyCgaKSptloVN--\r\n'
--compressed

Check the code as it is:

Embedded content: https://gist.github.com/kibolho/3be15d77d2a74bc3033743c36f6cb3c0.js

Then it was necessary to create a drawing and link it with the send for signature script.

Finally, we get information such as the name and email of the people who sign the document by selecting specific cells.

Conclusion

Through pre-existing APIs, it is possible to automate and deliver value to an audience that would have difficulty doing the process manually.