Google Sheets App Script To Call Github API Endpoint

[akibrhast]

Google Sheets App Script To Call Github API endpoint for creating a new issue based on form submission values.

var token = "YOUR_TOKEN";
var handle = "100Automations";
var repo = "futureautomations";
function onFormSubmit(e)
{

    const keysArray = [
      "Your Name",
      "Your Email",
      "Overview: describe the kind of repetitive thing you have to do and the benefit of automating it?",
      "Ownership of Idea",
      "Current State: Is this just an idea, or has any work already been done to build it?",
      "Languages or frameworks used?",
      "Platform?",
      "Automation triggers: What starts it? What's it responding to? Time or event based?",
      "How much manual or custom input is required?",
      "Output: What's the desired result? What do we not want to see?",
      "Project size: rough estimate of effort and time needed to build it",
      "Stakeholders and Impact - who benefits and how?",
      "Resources/Instructions: Has any documentation been created yet?",
      "Action Items/Research Needed"
    ];

    let bodyString = ``;
    for(const key of keysArray){
      //console.info(key,e.namedValues[key][0]);
      bodyString = bodyString.concat("## " + key + "\n" + e.namedValues[key][0] + "\n\n");
    }
    //console.info("test",bodyString);
    //console.log({message: 'Function Input', initialData: bodyString});

    let payload = {
        "title": 'Automation Idea Proposal',
        "body": bodyString,
        "labels":["role: administrative"],
        "assignees":["Olivia-Chiong"]
    };
 
    let options = {
        "method": "POST",
        "headers": {"Accept": "application/vnd.github.v3+json", "Authorization" : "token " + token},
        "contentType": "application/json",
        "payload": JSON.stringify(payload),
        "muteHttpExceptions": true
    };
    var response = UrlFetchApp.fetch("https://api.github.com/repos/"+handle+"/"+repo+"/issues", options);

}

[akibrhast]

@averdin2 @erikaBell @jbubar @danyc23

[akibrhast]

Github Api Endpoint

https://docs.github.com/en/rest/reference/repos#add-a-repository-collaborator

[erikaBell]

Links to full list of available Github Endpoints

[erikaBell]

@Aveline-art

[jbubar]

Here is akibs code

//  Enter sheet name where data is to be written below
const SHEET_NAME = "Sheet1";
const SHEET_NAME_2 = 'Sheet2';

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doGet(e){
  return handleResponse(e);
}

function doPost(e){
      // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success","data":e}))
          .setMimeType(ContentService.MimeType.JSON);

}
//Utility function to implement string.replaceAll()
String.prototype.replaceAll = function(search, replacement) {
        var target = this;
        return target.replace(new RegExp(search, 'g'), replacement);
};

//Clean Up Data For Display
function cleanUpData(readableObject){
  let mystring = '';

  for(item in readableObject){
      if(item == 'time' || item == 'join_date'){
        mystring += `${item} = ${ new Date(readableObject[item]*1000).toDateString() }${String.fromCharCode(10)}`;
      }
      else{
      mystring += `${item} = ${readableObject[item]}${String.fromCharCode(10)}`;
      }
  
  }

  return mystring;
}




function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    const doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));

    const sheet = doc.getSheetByName(SHEET_NAME);
    const sheet2 = doc.getSheetByName(SHEET_NAME_2);

    const readableObject = {};

    let sheet2NextRow = sheet2.getLastRow()+1;
    
    var headers = sheet.getRange(1, 1, 1, 13).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      row.push(e.parameter[headers[i]] || '""');
      readableObject[headers[i]] = e.parameter[headers[i]] || '""' ; 
      
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    
    sheet2.getRange(`b${sheet2NextRow}`).setValue(  cleanUpData(readableObject)     );

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow,"data":readableObject}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

[akibrhast]

Github action to sync app scripts on github to app scripts on google cloud

• https://github.com/marketplace/actions/clasp-token
• https://github.com/namaggarwal/clasp-token-action

[Aveline-art]

ToDo:

1. Get hackforla bot token for permissions from Bonnie
2. Put the scripts (see below) and forms by Erika Bell in the hackforla drive

Automation tasks for the script (draft):

• Add users to github
• Add users to slack
• Send an email to users
• Perform other misc hackforla-related task related to adding new onboarding users

Optional:

1. Add github actions bot to sync script from github to google script for the purposes of centralizing source code onto github and allowing version control

Possible relevant documentation:
Slack invite api: https://api.slack.com/methods/admin.users.invite
Google scripts REST api: https://developers.google.com/apps-script/api/reference/rest/v1/projects/updateContent

[akibrhast]

Invite Bonnie or olivia for backend meeting

[erikaBell]

Not necessarily related, but putting this here both for myself and anyone else who might one day find it useful: Various Lessons

Been using one of their lessons as a guide for something else and it's one of the most well written, clear documents on anything related code I've read (-': I could cry. I'm assuming the others are just as good.

[erikaBell]

Ava's Google Drive editor permissions test: https://gist.github.com/Aveline-art/970743447633716be2ca3e264ac3421c

Have not looked over it myself yet, putting here so there is easy access for all.