Skip to content

Instantly share code, notes, and snippets.

@toripiyo
Last active January 20, 2022 21:07
Show Gist options
  • Save toripiyo/1215f2da81fec5569990bf4ab2e8a0b1 to your computer and use it in GitHub Desktop.
Save toripiyo/1215f2da81fec5569990bf4ab2e8a0b1 to your computer and use it in GitHub Desktop.
create GitHub issues from selected range on Google SpreadSheet
const ui = SpreadsheetApp.getUi();
const userProperties = PropertiesService.getUserProperties();
const ACCESS_TOKEN = 'access token';
function setToken(){
const scriptValue = ui.prompt('Personal Access Token', ui.ButtonSet.OK);
userProperties.setProperty(ACCESS_TOKEN, scriptValue.getResponseText());
}
function removeToken(){
userProperties.deleteProperty(ACCESS_TOKEN);
ui.alert('Token is removed.');
}
// github issue creation endpoint
const githuburl = (organization_name, repository) => `https://api.github.com/repos/${organization_name}/${repository}/issues`;
// request creation function
const create_request = (url, accesstoken) => {
return (payload) =>
({
url: url,
method: "post",
headers: {
Authorization : `token ${accesstoken}`
},
payload: payload
});
};
const create_issue = () => {
// obtain sheet information
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const activeRange = sheet.getActiveRange();
const issueRows = activeRange.getValues();
const issues = issueRows.map( row => {
const title = row[0];
const body = `
### Due Date
${row[1].getFullYear()}/${row[1].getMonth() +1}/${row[1].getDate()}
### Operation Detail
${row[2]}
### Other
${row[3]}
`
const labels = row[4] ? row[4].split(',') : [];
return {title: title, body: body, labels: labels}
})
// variables
const accesstoken = userProperties.getProperty(ACCESS_TOKEN);
const organization_name = 'toripiyo';
const repository = 'sample-rails-app';
// url
const url = githuburl(organization_name, repository);
// create request function
const request = create_request(url, accesstoken);
// request creation
const requests = issues.map(issue => request(JSON.stringify(issue)));
Logger.log(requests);
// send issue creation request
UrlFetchApp.fetchAll(requests);
};
// confirmation
const confirmation = () => {
const confirmed = Browser.msgBox("Do you create github issue?", Browser.Buttons.OK_CANCEL);
if(confirmed == 'ok') {
create_issue();
}
};
// add menus
const onOpen = () => {
const GitHubMenu = [
{name: "create Issue", functionName: "confirmation"},
{name: "set Token", functionName: "setToken"},
{name: "remove Token", functionName: "removeToken"}
]
SpreadsheetApp.getActiveSpreadsheet().addMenu("GitHub",GitHubMenu); // add menu
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment