Abílio Azevedo.

Automating the Digital Signature of an Excel Spreadsheet

Cover Image for Automating the Digital Signature of an Excel Spreadsheet
Abílio Azevedo
Abílio Azevedo

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 Existe uma API para isso

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. Desenho vinculado com 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.

Por hoje é só pessoal


More posts

Cover Image for The Phychology of Money

The Phychology of Money

Morgan Housel offers valuable insights on financial management and decision-making. The author emphasizes that financial success depends more on behavior than on intelligence or technical knowledge. Housel highlights the importance of long-term vision and resilience in the face of market volatility, encouraging us to focus on sustainability rather than short-term gains.

Cover Image for Bellsant

Bellsant

I've been at the forefront of developing a cutting-edge health and wellness app. Our tech stack combines React Native for cross-platform mobile development with a serverless NodeJS backend, leveraging AWS Lambda for scalability and cost-efficiency.

Abílio Azevedo
Abílio Azevedo

NewsLetter

I will send the content posted here. No Spam =)

Experienced Software Engineer with degree in Electrical Engineering with over 10 years of hands-on expertise in building robust and scalable mobile, web and backend applications across various projects mainly in the fintech sector. Mobile (React Native), Web (React and Next.JS) and Backend (Node.JS, PHP and DJANGO). My goal is to create products that add value to people. - © 2024, Abílio Azevedo