I am working on a discord bot that will take random values from a Google Spreadsheet I have created. I have written my accessSpreadsheet.js file so that it will fetch data from Google Sheets, this is done asynchronously. I can console.log out the data I want from the async functions but I cannot set other variables by returning the asynchronous data, it instead returns undefined.
To start I have a discord.js that accepts input from Discord and calls my giRandom function and replys to the user with the output.
if(input === "!0"){
var output = giRandom("1"); //this seed just guarantees a "true" outcome for testing the boolSet function (located below)
msg.reply(output); //Output's the returned giRandom data (currently replies but output is null)
}
Discord is calling the following function from giRandom.js Everything seems to work here until we reach var output = builder(seed_data);
//Returns a random number when fed a seed
function giRandom(seed) {
//Seperate random seed js file. Working properly.
var seedrandom = require('seedrandom');
var rng = seedrandom(seed);
var seed_Data = seedData(rng()); //Function below
var output = builder(seed_Data); //Function below //Returning Undefined
return output; //Returns output to discord.js (msg.reply(output))
}
exports.giRandom = giRandom; //located at end of file, not in a function
My giRandom function is calling seedData and builder functions (both located within giRandom.js)
seedData function
//Takes the seed generated by seedrandom (something like 0.1189181568115987) and seperates each digit into an array, ignoring NaN digits (in this case the decimal point)
function seedData(data){
var counter = 0; //used to determine amount of NaN digits
//Counts each NaN in inputArray
var inputArray = Array.from(data.toString()).map(Number);
for (var i = 0; i < inputArray.length; i++) {
if (inputArray[i] >= 0) { //Checks for NaN
if (debug) {
console.log("inputArray[" + i + "] | " + inputArray[i]);
}
} else { //If NaN counter++
counter++;
}
}
var outputArray = new Array(counter); //Creates output array with correct size for all NaN values
counter = 0; //Reset counter to 0
//Creates outputArray containing all seed digits
for (var i = 0; i < inputArray.length; i++) {
if (inputArray[i] >= 0) {
outputArray[counter] = inputArray[i];
counter++;
}
}
//Debug function to view output values
for (var i = 0; i < outputArray.length; i++) {
if (debug) {
console.log("outputArray[" + i + "] | " + outputArray[i]);
}
}
return outputArray; //returns outputArray to seed_Data in giRandom function
}
builder function
//Takes seed_Data from giRandom function and determines the value's output
function builder(data) {
//Booleans, determine if each category returns a value based on boolSet() (function below)
var raceBool;
//data[0] is always a value of 0. 16 Total Usable values 1-17
raceBool = boolSet(data[1]);
if (raceBool == true) {
var raceData = sheetToArray(1);
console.log("raceData | " + raceData);
return raceData; //Returning undefined
}
}
My builder function is calling boolSet and sheetToArray functions (both located within giRandom.js)
boolSet function
//Each seed digit can be 0-9. If the digit passed (boolData) matches the value for valueTrue, valueFalse, or valuePick the output will be set accordingly. (Currently will always output True)
function boolSet(boolData) {
var valueTrue = [0, 1, 2, 3];
var valueFalse = [4, 5, 6, 7];
var valuePick = [8, 9];
//Loop through valueTrue and compare with boolData
for (var i = 0; i <= valueTrue.length; i++) {
if (boolData == valueTrue[i]) {
if (debug) {
console.log("boolData | " + boolData + " | true");
}
return true;
}
}
//Loop through valueFalse and compare with boolData
for (var i = 0; i <= valueFalse.length; i++) {
if (boolData == valueFalse[i]) {
if (debug) {
console.log("boolData | " + boolData + " | false");
}
return false;
}
}
//If !true && !false, must be "pick". This value will allow the end user to choose their data (in this case race)
return "pick";
}
sheetToArray function
//takes an int for sheetNum that determines which sheet in the project to load
function sheetToArray(sheetNum){
var sheetArray = []; //Array that holds all rows of a particular worksheet, used to build spreadsheetArray
var cellArray = []; //Array that holds all cells in a particular row of the worksheet used to build sheetArray
var rowArrayCount = 1; //Counter to determine rows in cells forEach loop
/*I believe this is where my problem probably lies*/
//Calling async function accessSpreadsheet from spreadsheet.js
accessSpreadsheet(sheetNum).then(function (arr) {
var cells = arr;
//loop through all cells in current sheet
cells.forEach(cell => {
if (cell.row > rowArrayCount) { //True determines that data is now on the next row of current sheet
rowArrayCount++;
sheetArray.push(cellArray); //push last row to sheetArray
cellArray = []; //reset cellArray for new row
}
cellArray.push(cell._value); //push final row to cellArray
})//Exit forEach
sheetArray.push(cellArray); //push all rows to sheetArray
var raceLength = sheetArray.length; //Returns value correctly
console.log("raceLength | " + raceLength); //Returns value correctly
console.log("sheetArray | " + sheetArray); //Returns value correctly
return sheetArray; //Returns undefined
})
}
To access Google Sheets I have a seperate js file. This is where the asynchronous function accessSpreadsheet() is
accessSpreadsheet.js
//Google Sheet Access Const
const GoogleSpreadsheet = require('google-spreadsheet');
const { promisify } = require('util');
const creds = require('./client_secret.json');
const doc = new GoogleSpreadsheet('**************'); //Google sheet we have access to (removed for security)
module.exports = accessSpreadsheet = async (sheetNum) => {
await promisify(doc.useServiceAccountAuth)(creds);
const info = await promisify(doc.getInfo)();
const sheet = info.worksheets[sheetNum];
//Async get sheet's cells without an offset
const cells = await promisify(sheet.getCells)({
offset: 0
})
return cells;
}
