Skip to content

Instantly share code, notes, and snippets.

@hnykda

hnykda/appscript.gs

Last active August 1, 2024 13:38
Show Gist options
  • Save hnykda/684b1ae9e26e622b657af51c3095b9a8 to your computer and use it in GitHub Desktop.
Save hnykda/684b1ae9e26e622b657af51c3095b9a8 to your computer and use it in GitHub Desktop.
Download ZIP
docuseal googlesheet public bulk send
Raw
appscript.gs
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Submissions')
.addItem('Process Scholarships', 'processScholarshipsMenu')
.addItem('Process Waivers', 'processWaiversMenu')
.addToUi();
}
function processScholarshipsMenu() {
processSubmissions('scholarships');
}
function processWaiversMenu() {
processSubmissions('waivers');
}
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 if (type === 'waivers') {
result = sendCampWaivers();
} 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 = 'https://sign.fabric.camp/api/submissions';
var authToken = 'xeWjngrPvu53x2tmBiHHe2qhHYFipqWhvCKdaRmEkYB';
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 = 'https://sign.fabric.camp/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];
var amount = Number(row[1]); // Corrected comment: setupper::amount
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) {
continue;
}
var holderEmail = row[0] && row[0].trim()
var providerEmail = row[2] && row[2].trim()
// Validate row before sending
if (row[3] === true && Number.isInteger(amount) && amount > 0
&& emailPattern.test(holderEmail) && emailPattern.test(providerEmail)
&& row[4] === "") {
var postData = {
"template_id": SCHOLARSHIP_TEMPLATE_ID,
"order": "random",
"submitters": [
{
"role": "holder",
"email": holderEmail,
},
{
"role": "provider",
"values": { "amount": amount },
"fields": [{ "name": "amount", "readonly": true }],
"email": providerEmail // 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 };
}
function sendCampWaivers() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Waivers");
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];
if (row[0] === "" || row[2] === false) {
continue;
}
if (row[2] === true && emailPattern.test(row[0]) && row[3] === "") { // Assuming TRUE means can send
var postData = {
"template_id": WAIVERS_TEMPLATE_ID,
"order": "random",
"submitters": [
{
"role": "signatory",
"email": row[0],
"values": { "Camp Name": row[1] },
"fields": [{ "name": "amount", "readonly": true }],
},
]
};
// Reuse the sendSubmissionRequest function
var result = sendSubmissionRequest(postData, i, 4);
if (!result.success) {
errors.push(`Row ${i}: ${result.error}`);
} else {
successes.push(`Row ${i}: Submission successful with ID ${result.submissionId}`);
}
}
}
return { errors, successes };
}
Raw
input.csv
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
@hnykda
Copy link
Author

hnykda commented Feb 20, 2024

Cross posting from here:

One thing we were missing when migrating from the outrageously expensive DocuSign was an ability to produce multiple submissions with some customized fields per the submission. Think about sending a contract to 10 different people, each of them having different rate. I created a simple Google App Script that you can attach to your Google Spreadsheet, it adds a button to the Google Spreadsheets UI top menu bar, and when you click it, it will read the data from the sheet's rows and prefill the corresponding fields to the given template and create the submissions. You have to tweak this to your specific case (at least the template ID, number of columns, ...).
The script and an example of CSV which this works with (when imported to Google Spreadsheets) is on this gist.
Enjoy!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment