Last active
February 20, 2024 12:34
-
-
Save hnykda/684b1ae9e26e622b657af51c3095b9a8 to your computer and use it in GitHub Desktop.
docuseal googlesheet public bulk send
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Submissions') | |
.addItem('Process Scholarships', 'processScholarshipsMenu') | |
.addToUi(); | |
} | |
function processScholarshipsMenu() { | |
processSubmissions('scholarships'); | |
} | |
function processSubmissions(type) { | |
var ui = SpreadsheetApp.getUi(); | |
var response = ui.alert(`Proceed with processing ${type}?`, ui.ButtonSet.YES_NO); | |
if (response != ui.Button.YES) { | |
ui.alert('Processing canceled.'); | |
return; | |
} | |
var result; | |
if (type === 'scholarships') { | |
result = sendScholarships(); | |
} else { | |
ui.alert('Invalid type specified.'); | |
return; | |
} | |
// Reporting | |
if (result.errors.length > 0) { | |
var errorMessage = "Errors occurred:\n" + result.errors.join("\n"); | |
Logger.log(errorMessage); | |
ui.alert(errorMessage); | |
} | |
if (result.successes.length > 0) { | |
var successMessage = "Successes:\n" + result.successes.join("\n"); | |
Logger.log(successMessage); | |
ui.alert(successMessage); | |
} else { | |
ui.alert('No successful submissions.'); | |
} | |
} | |
function sendSubmissionRequest(submissionData, rowIndex, updateColumnIndex) { | |
var apiUrl = '{{YOUR-DOCUSEAL-URL}}'; | |
var authToken = '{{YOUR-API-URL}}'; | |
var options = { | |
'method': 'post', | |
'contentType': 'application/json', | |
'headers': { | |
'X-Auth-Token': authToken | |
}, | |
'payload': JSON.stringify(submissionData) | |
}; | |
try { | |
var response = UrlFetchApp.fetch(apiUrl, options); | |
var jsonResponse = JSON.parse(response.getContentText()); | |
// for debugging: | |
//Logger.log(options) | |
//var jsonResponse = [{ submission_id: 3 }] | |
var submissionId = jsonResponse[0].submission_id | |
if (jsonResponse && jsonResponse.length > 0) { | |
var submissionUrl = apiUrl + '/submissions/' + submissionId; | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
sheet.getRange(rowIndex + 1, updateColumnIndex).setValue(submissionUrl); | |
return { success: true, submissionId: submissionId }; | |
} | |
} catch (error) { | |
Logger.log('Error sending request for row ' + rowIndex + ': ' + error.toString()); | |
return { success: false, error: error.toString(), rowIndex: rowIndex }; | |
} | |
return { success: false, error: error.toString(), rowIndex: rowIndex }; | |
} | |
function sendScholarships() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scholarship"); | |
var range = sheet.getDataRange(); | |
var values = range.getValues(); | |
var errors = []; // To store error messages | |
var successes = [] | |
var emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; | |
for (var i = 1; i < values.length; i++) { | |
var row = values[i]; | |
// row: 0 is holder::email, 1 is holder::amount, 2 is provider::email, 3 is 'can send', 4 is "request sent" | |
var amount = Number(row[1]); | |
Logger.log("Processing row %s: %s, %s, %s", i, row[0], amount, row[2]); | |
// if empty, then just skip the row | |
if (row[0] === "" || row[3] === false) { | |
Logger.log("Row is empty or 'can send' is not checked") | |
continue; | |
} | |
// Validate row before sending, that the amount is number, can send is true, email is email, and the submission hasn't been sent yet | |
if (row[3] === true && Number.isInteger(amount) && amount > 0 | |
&& emailPattern.test(row[0]) && emailPattern.test(row[2]) | |
&& row[4] === "") { | |
// this is where you need to tailor the payload to your template. You need to change the template_id (in the URL | |
// of the template page in DocuSeal), the roles and the fields. You have to make sure it matches to the | |
// rows in the source spreadsheet. It currently relies on the order, so things will break unless you adjust | |
var postData = { | |
"template_id": 2, | |
"submitters": [ | |
{ | |
"role": "holder", | |
"email": row[0], // holder::email | |
"values": { "amount": amount }, | |
"fields": [{ "name": "amount", "readonly": true }], | |
}, | |
{ | |
"role": "provider", | |
"email": row[2] // provider::email | |
} | |
] | |
}; | |
// Use the abstracted function to send the request | |
var result = sendSubmissionRequest(postData, i, 5); | |
if (!result.success) { | |
errors.push(`Row ${i}: ${result.error}`); | |
} else { | |
successes.push(`Row ${i}: Submission successful with ID ${result.submissionId}`); | |
} | |
} else { | |
Logger.log('Row ' + (i + 1) + ' skipped due to validation failure or it has already been processed.'); | |
} | |
} | |
return { errors, successes }; | |
} | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
holder::email | holder::amount | provider::email | can send | request sent / submission link | |
---|---|---|---|---|---|
some-email@gmail.com | 2000 | another@gmail.com | TRUE | ||
hey-email@gmail.com | 1200 | another@gmail.com | TRUE |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Cross posting from here: