Last active
August 1, 2024 13:38
-
-
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') | |
.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 }; | |
} |
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: