Skip to content

Instantly share code, notes, and snippets.

@jhannes
Last active May 23, 2023 14:45
Show Gist options
  • Save jhannes/31baa62b0aae62d5867e29c6824130e3 to your computer and use it in GitHub Desktop.
Save jhannes/31baa62b0aae62d5867e29c6824130e3 to your computer and use it in GitHub Desktop.
Google Forms to Trello
var authentication = "key=a...&token=9...";
var idBoard = "592....";
var inboxList = "592...";
var updatedLabel = "592....";
function getField(itemResponses, fieldName) {
var titles = [];
for (var i in itemResponses) {
if (itemResponses[i].getItem().getTitle() === fieldName && itemResponses[i].getResponse().length > 0) {
return itemResponses[i].getResponse();
}
titles.push(itemResponses[i].getItem().getTitle());
}
throw "Field [" + fieldName + "] not found in [" + titles + "]";
}
function getCardName(response) {
var itemResponses = response.getItemResponses();
return getField(itemResponses, "Tittel") +
" (" + getField(itemResponses, "Foredragsholders navn") +
" - " + getField(itemResponses, "Arbeidsgiver") + ")";
}
function getCardLabelIds(response) {
var itemResponses = response.getItemResponses();
var labelResponse = UrlFetchApp.fetch("https://api.trello.com/1/boards/" + idBoard + "/labels?" + authentication);
var labelsJson = JSON.parse(labelResponse.getContentText());
var idLabels = labelsJson.filter(function(label) {
return label.name === getField(itemResponses, "Topic of your talk");
}).map(function(label) { return label.id });
if (!idLabels.length) {
idLabels = labelsJson.filter(function(label) {
return label.name === "Other";
}).map(function(label) { return label.id });
}
var idLabelsLength = labelsJson.filter(function(label) {
return label.name === getField(itemResponses, "Length of your talk");
}).map(function(label) { return label.id });
if (idLabelsLength.length > 0) {
idLabels.push(idLabelsLength[0]);
}
return idLabels;
}
function getCardDesc(response) {
var itemResponses = response.getItemResponses();
var body = "";
for (var j in itemResponses) {
var itemResponse = itemResponses[j];
if (itemResponse.getResponse().length == 0) {
continue;
}
if (itemResponse.getItem().getType() == FormApp.ItemType.PARAGRAPH_TEXT) {
body += "\n## " + itemResponse.getItem().getTitle() + "\n" + itemResponse.getResponse() + "\n\n";
} else {
body += "**" + itemResponse.getItem().getTitle() + ":** " + itemResponse.getResponse() + "\n";
}
}
return body;
}
function saveToTrello(args) {
var response = args.response;
var cardId = PropertiesService.getDocumentProperties().getProperty("trello_card_id_" + response.getId());
if (cardId) {
updateCard(cardId, response);
} else {
createCard(response);
}
}
function sendAll() {
var responses = FormApp.getActiveForm().getResponses();
for (var i in responses) {
saveToTrello({response: responses[i]});
}
}
function updateCard(cardId, response) {
var idLabels = getCardLabelIds(response);
idLabels.push(updatedLabel);
var payload = {
name: getCardName(response),
desc: getCardDesc(response),
closed: false,
idLabels: idLabels.join(',')
};
var url = 'https://api.trello.com/1/cards/' + cardId + '?' + authentication;
var resp = UrlFetchApp.fetch(url, {"method" : "PUT", "payload" : payload, muteHttpExceptions: true});
Logger.log(resp);
if (resp.getResponseCode() == 404) {
createCard(response);
}
}
function createCard(response) {
var payload = {
name: getCardName(response),
desc: getCardDesc(response),
idList: inboxList,
idLabels: getCardLabelIds(response).join(',')
};
var url = 'https://api.trello.com/1/cards?' + authentication;
var resp = UrlFetchApp.fetch(url, {"method" : "POST", "payload" : payload});
Logger.log(resp);
var responseJson = JSON.parse(resp.getContentText());
PropertiesService.getDocumentProperties().setProperty("trello_card_id_" + response.getId(), responseJson.id);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment