Creating a Log tab of sent data using Google Sheets Apps Script

Question

I am creating a text message sending service within Google Sheets using Google Apps Script and the Twilio SMS service. I've been able to successfully create that service, but I am now looking to add a "Sent Log" in a different tab so that I can see a historical list of phone numbers, the date and status their message was sent, and the message body included with that text.

The phone numbers, the date and status, and the message body already exist in the sheet. However, the message body is different than the other 2 values in that it is the same for all messages, and does not repeat on each row like the others. In my attempts to copy the information to another sheet and append a new row to the log for each message that was sent, I've only been able to copy the recipient phone number and status+date columns. I want to produce a log that includes all 3 - phone number, status+date, and message body using the script.

Google sheet - https://docs.google.com/spreadsheets/d/1Ni3Wj8MLH6Advcie5Wbu9A5sSj52OY5UV2dgqLU5MSk/edit?usp=sharing

My recipient phone numbers, status and date sent, and message body text are all in a tab labeled Sending List. Thanks in advance.

function AddRowsToLog(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('Sending List');
  var outputSheet = ss.getSheetByName('Log');
  var msg = ss.getSheetByName('Sending List').getRange('E1').getValues();
  var dataRows = dataSheet.getLastRow() - 1;
  var dataValues = dataSheet.getRange(2,1,dataRows,2).getValues(); 
//How do I choose the content without selecting it and still have it be dynamic so that even if
//I have a different number of recipients each time, the number of rows logged match up?
 
  for (var i = 0; i < dataRows; i++) {
   Logger.log([dataValues[i],msg]);
   outputSheet.appendRow([dataValues[i],msg]);   
  }
}

Cooper Cooper · Accepted Answer · 2019-10-12T00:05:04

Try this:

function AddRowsToLog(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet=ss.getSheetByName('Sending List');
  var outputSheet = ss.getSheetByName('Log');
  var msg = dataSheet.getRange('E1').getValue();
  var dataValues = dataSheet.getRange(2,1,dataSheet.getLastRow()-1,3).getValues(); 
  for(var i=0;i<dataValues.length;i++) {
    dataValues[i].splice(dataValues[i].length,0,msg);//splice msg to end of row
    outputSheet.appendRow(dataValues[i]);   
  }
}

Actually, this is much faster:

function AddRowsToLog(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet=ss.getSheetByName('Sending List');
  var outputSheet = ss.getSheetByName('Log');
  var msg = dataSheet.getRange('E1').getValue();
  var dataValues = dataSheet.getRange(2,1,dataSheet.getLastRow()-1,3).getValues(); 
  for(var i=0;i<dataValues.length;i++) {
    dataValues[i].splice(dataValues[i].length,0,msg);
    //outputSheet.appendRow(dataValues[i]);   
  }
  outputSheet.getRange(outputSheet.getLastRow()+1,1,dataValues.length,dataValues[0].length).setValues(dataValues);
}

Creating a Log tab of sent data using Google Sheets Apps Script

1 Answers