How to Send SMS Messages Using Google Sheets and Your Android Phone

Do you want to send personalized bulk text messages to phone numbers in your contact list?

In this article, we will explore sending SMS messages directly from your phone’s SIM card, using an Android device with the assistance of Google Sheets, without the need for third-party SMS services. In other words, we will build your own text messaging app with Google Sheets and MIT’s App Inventor.

App Inventor is a programming tool designed for everyone, including children, allowing easy manipulation with drag-and-drop command blocks. App Inventor is released as free software, and you can easily log in with your Google account. With the App Inventor tool, Google facilitates people in building software applications for Android devices. Programming with this app is not limited to simple games; you can also create software to provide information or serve your specific needs.

Step 1: Prepare Source Data in Google Sheets

With this SMS application, you can send text messages to any phone number, including international numbers if the international texting feature is enabled on your phone. You will be charged standard text messaging rates according to your mobile plan. However, sending your messages will be more convenient as the content is pre-composed and personalized on Google Sheets. You won’t have to type each message on your tiny phone.

Here is an example of the sample spreadsheet for this demonstration.

You can create multiple columns in Google Sheets based on your needs, but it is essential to have the following three data columns:

  • Phone (for the phone number of your contacts)
  • Status (whether the SMS was sent to that phone or not)
  • Text (personalized text message)

Use the ARRAYFORMULA function below to create a text message string from various columns:

= ArrayFormula( IF (NOT (ISBLANK(A2:A)), A2:A & “ “ & B2:B & “ - I will see you in “ & C2:C, ))

Now, with your source data ready in the spreadsheet, we will use Google Apps Script to convert the Google Sheets data into an API. This will enable our Android app to read spreadsheet data with a simple HTTPS request.

Step 2: Convert Google Sheets Data into an API

Inside the spreadsheet, go to Tools >> Script Editor and paste the code snippet below:

const SHEET_URL = “YOUR_GOOGLE_SHEET_URL;
const SHEET_NAME = "SMS";
const doGet = () => {
const sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(
SHEET_NAME
);
const [header, ...data] = sheet.getDataRange().getDisplayValues();
const PHONE = header.indexOf('Phone');
const TEXT = header.indexOf('Text');
const STATUS = header.indexOf('Status');
const output = [];
data.forEach((row, index) => {
if (row[STATUS] === '') {
output.push([index + 1, row[PHONE], row[TEXT]]);
}
});
const json = JSON.stringify(output);
return ContentService.createTextOutput(json).setMimeType(
ContentService.MimeType.TEXT
);
};
const doPost = (e) => {
const sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(
SHEET_NAME
);
const [header] = sheet.getRange('A1:1').getValues();
const STATUS = header.indexOf('Status');
var rowId = Number(e.parameter.row);
sheet.getRange(rowId + 1, STATUS + 1).setValue('SMS Sent');
return ContentService.createTextOutput('').setMimeType(
ContentService.MimeType.TEXT
);
};

Next, navigate to the Publish menu in the Google Script Editor, select “Deploy as web app.” Choose “Me” in the “Execute the App” settings and “Anyone, even anonymous” in the “Who has access” settings.

Click the Deploy button, and you will see a secret API URL that we will request in the next step. Never share this API URL with anyone.

Now, our spreadsheet API is ready. In the next step, you will build an Android app that will read the list of text messages and phone numbers from Google Sheets and send SMS messages. The messages will be sent directly from your phone’s SIM card instead of using any third-party SMS gateway services.

Step 3: Build an SMS App for Android

Normally, you would need to know programming languages like Flutter or Java to create an Android app, but in this guide, we will use MIT App Inventor, a simple way to develop fully functional apps through drag-and-drop.

Log in to the website appinventor.mit.edu with your Google account and create a new App. While in design mode, drag the following components into your app:

  • User Interface, ListView -> To display the list of messages downloaded from Google Sheets.
  • User Interface, Button -> To refresh messages from Google Sheets and send SMS messages from the Android app.
  • Connectivity, Web -> To perform GET and POST requests to Apps Script.
  • User Interface, Notifier -> To display progress bars and alerts.
  • Social, Texting -> To send SMS messages.

Next, switch to the Blocks section inside App Inventor and design the blocks as shown in the image below.

Next, go to the Build menu inside App Inventor, select App (provide QR code for .apk), and use your phone to scan the QR code. This step is to download the APK file to your device. Simply install this APK file, and you can start sending your messages.

Hopefully, through this article, you have learned how to automatically send bulk text messages on your phone using Google Sheets.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top