[PYTHON] Create a script for your Pepper skill in a spreadsheet and load SayText directly from the script

What to use

• Google SpreadSheet
• Google Apps Script
• Choregraphe
• Pepper

Description

When I made a script for Pepper, I used to use Excel etc., but when developing with multiple people or because I want to make it anywhere on the browser, I started using Google SpreadSheet. I also input the adjusted conversation in the script and save it, so it is troublesome to copy and paste each time and update the distributed package, so I made it possible to read it as it is from the script. ..

Therefore, I will describe each item as a memo. Google SpreadSheet

I am making a script like this.

From the left

1. ID --Dialog box name --The name of the page to display on the display --Conditions --Button --Motion number and type --Serif (human language) --Serif (Pepa)

Items may change slightly depending on the content of the skill, but basically the script is made like this. After creating the script, "** https: //docs.google.com/spreadsheets/d/" in the URL of SpreadSheet is followed by " XXXXXXXXXXXXXXXX (Book ID) **" and "**XXXXXXXXXXXXXXXX (Book ID) " after that. Make a note of the number " 1234567 (Sheet ID) " after " / edit # gid = **". These are used when sending a request from Choregraphe.

Google Apps Script

SayText.gs

//Email address to send when an error occurs
var managerAddr = "[email protected]";


function doGet(e) {
  //Receives a parameter named param1.
  var json = JSON.parse(e.parameter.param1);
  //Open the parameter BookID spreadsheet
  var ss = SpreadsheetApp.openById(decodeURI(json["sayDb_id"]));
  //Get Sheet in Open Spreadsheet
  var sheets = ss.getSheets();
  
  for (var i in sheets){
    var sheet = sheets[i];
    //Start processing when the sheet ID specified by the parameter is found
    if (sheet.getSheetId() == json["db_id"]){
      var say_txt = getSayTxt(sheet);
    }
  }
  //Return with json
  return ContentService.createTextOutput(JSON.stringify(say_txt)).setMimeType(ContentService.MimeType.JSON);
}

function getSayTxt(sheet) {
  var sCol = 1;
  var sRow = 2;
  var lRow = sheet.getLastRow();
  
  //The first line is"id"Find the column with the name
  var sayIdCol = getSayTxtCol(sheet, "id");
  //Similarly"pepaLanguage"Find the column with the name
  var sayTxtCol = getSayTxtCol(sheet, "pepaLanguage");
  //If not found, return with an error.
  if (sayTxtCol == 0 || sayIdCol == 0){
    GMailApp.sendMail(managerAddr, "getSay runtime", "There seems to be a sheet where the column cannot be found." + sheet.getSheetId());
    Logger.log("Columns is not found");
    return [];
  }
  
  //"id"When"pepaLanguage"の位置を確認しておかしなこWhenにならないようにしてる。
  if (sayIdCol > sayTxtCol){
    var data = sheet.getRange(sRow, sayTxtCol, lRow, sayIdCol).getValues();
  }else{
    var data = sheet.getRange(sRow, sayIdCol, lRow, sayTxtCol).getValues();
  }
  var json = new Object();
  var jsonArray = [];
  //I will add
  for (var i = 0; i < data.length -1; i++){
    var val = data[i][sayIdCol - 1];
    if (val != "" && val != null){
      json[String(data[i][sayIdCol - 1])] = data[i][sayTxtCol - 1];
    }
  }
  jsonArray.push(json);
  return jsonArray;
}
  
function getSayTxtCol(sheet, title){
  var lCol = sheet.getLastColumn();
  for (var i = 1; i <= lCol; i++){
    if (sheet.getRange(1, i).getValue() == title){
      return i;
      break;
    }
  }
  return 0;
}

Choregraphe (Python) If you look at Python, you can see it.

LoadSayText.py

class MyClass(GeneratedClass):
    def __init__(self):
        GeneratedClass.__init__(self)

    def onLoad(self):
        self.memory = ALProxy("ALMemory")

    def onUnload(self):
        self.memory = None

    def onInput_onStart(self):
        import urllib, urllib2, json
        #Make parameters.
        #In the actual application, it is passed in memory.
        p = {
            "sayDb_id":XXXXXXXXXXXXXXXX(Book ID),
            "db_id":1234567(Sheet ID)
            }
        data ={}

        try:
            data['param1'] = json.dumps(p)
            url_values = urllib.urlencode(data)
            full_url = '%s?%s' % url(api URL), url_values)
            request = urllib2.urlopen(full_url)
            self.json = json.loads(request.read(), "utf-8")[0]

            if len(self.json) == 0:
                self.onNotFound()
                return
            #Save the returned value in memory.
            #Call the saved Text with SayBox and let it speak.
            for key, value in self.json.items():
                self.memory.insertData("SayText/%d" % int(key), value.encode('utf-8'))
            self.onStopped()
        except Exception as e:
            self.logger.warn('==========error contents==========')
            self.logger.warn(e)
            self.logger.warn('============================')
            self.onError()

the end

I found a place where I was writing and a little error was likely to occur.