I have a spreadsheet of data where I pull data from an array of data. After I declare the data, I run the code and it sends a message to slack using their block kit.
Issue is I have to manually input what values of the data should be. I was wondering if there is a way to pull data for my message payload>post it to the channel>then repeat this process till no more data is left.
Example Spreadsheet will look like this below:
| Greeting | Word |
|---|---|
| Holla | Mundo |
| Hello | World |
Right now to pull my row and send a message I have it set up like this;
function main() {
// Grabbing data from Google Sheet
data = getGoogleSheetData(SHEET_ID)
Logger.log(data)
//Variables for data
Data1 = data[0][0]
Data2 = data[0][2]
However I have to always change the first array to match up with the next line of data, Then run it.
So if I want to send my message once more I just change the values to this then run it.
//Variables for data
Data1 = data[1][0]
Data2 = data[1][2]
Hopefully that makes sense. Any guidance is of course much appreciated
Edit***
Here is the code example:
// Method use to post in Slack channel
function sendAlert(payload) {
const webhook = SLACK_URL; //Paste your webhook URL here
var options = {
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload": JSON.stringify(payload)
};
try {
UrlFetchApp.fetch(webhook, options);
} catch(e) {
Logger.log(e);
}
}
// Defining the data range and vaules to grab from a Google Sheet
function getGoogleSheetData(GoogleSheetId) {
sheet_data = SpreadsheetApp.openById(GoogleSheetId)
let data = sheet_data.getSheetByName('sheet1').getRange("A1:AP17").getValues();
return data
}
function main() {
// Grabbing data from Google Sheet
data = getGoogleSheetData(SHEET_ID)
Logger.log(data)
//Variables for data
Date = data[0][0]
Contact_ID = data[0][2]
Sources = data[0][9]
Offer = data[0][10]
Accepted_Declined = data[0][12]
Recruiter = data[0][19]
Posting_Title = data[0][18]
Question_1 = data[0][33]
Question_2 = data[0][34]
Question_3 = data[0][35]
Question_4 = data[0][36]
Question_5 = data[0][37]
Question_6 = data[0][38]
Question_7 = data[0][39]
Question_8 = data[0][40]
Question_9 = data[0][41]
// check to see if Question_1 is null
if(Question_1.length != 0){
Logger.log(data);
} else
{
Question_1 = "N/A"
}
// Check if Question_2 is null
if(Question_2.length != 0) {
Logger.log(data);
} else
{
Question_2 = "N/A"
}
// Check if Question_3 is null
if(Question_3.length != 0){
Logger.log(data);
} else
{
Question_3 = "N/A"
}
// Check if Question_4 is null
if(Question_4.length != 0){
Logger.log(data);
} else
{
Question_4 = "N/A"
}
// Check if Question_5 is null
if(Question_5.length != 0){
Logger.log(data);
} else
{
Question_5 = "N/A"
}
// Check if Question_6 is null
if(Question_6.length != 0){
Logger.log(data);
} else
{
Question_6 = "N/A"
}
// Check if Question_7 is null
if(Question_7.length != 0){
Logger.log(data);
} else
{
Question_7 = "N/A"
}
// Check if Question_8 is null
if(Question_8.length != 0){
Logger.log(data);
} else
{
Question_8 = "N/A"
}
// Check if Question_9 is null
if(Question_9.length != 0){
Logger.log(data);
} else
{
Question_9 = "N/A"
}
message_payload = {
"blocks": [
{
"type": "header",
"text": {
"type": "plain_text",
"text": "Candidate Experience Survey Responses :lever:",
"emoji": true
}
},
{
"type": "divider"
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Date Applied*\n" + String(Date),
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*ID*\n" + String(Contact_ID)
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Source*\n" + String(Sources)
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Offer*\n" + String(Offer)
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Current Status*\n" + String(Accepted_Declined)
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Recruiter*\n" + String(Recruiter)
}
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": "*Posting Title*\n" + String(Posting_Title)
}
},
{
"type": "section",
"fields": [
{
"type": "mrkdwn",
"text": "*question 1*\n" + String(Question_1)
},
{
"type": "mrkdwn",
"text": "*question 2*\n" + String(Question_2)
},
{
"type": "mrkdwn",
"text": "question 3*\n" + String(Question_3)
},
{
"type": "mrkdwn",
"text": "*question 4*\n" + String(Question_4)
},
{
"type": "mrkdwn",
"text": "Question 5\n" + String(Question_5)
},
{
"type": "mrkdwn",
"text": "Question 6\n" + String(Question_6)
},
{
"type": "mrkdwn",
"text": "Question 7*\n" + String(Question_7)
},
{
"type": "mrkdwn",
"text": "Question 8\n" + String(Question_8)
},
{
"type": "mrkdwn",
"text": "Question 9\n" + String(Question_9)
}
]
}
]
}
test = sendAlert(message_payload)
Logger.log(test)
Logger.log(test)
The Spread Sheet looks like has the data in each line.
Right now I change the data manually in my code.
So I ran the code first for the arrays of data [0][0] and so on then change it to [1][0] and so on till I am out of data in spreadsheet.
Looking to find a way of doing that automatically in the code instead of me manually changing the values then running it.
Hopefully that explains it better.
