Learn How to Send SMS Messages with Google Sheets and your Android Phone

Using Google Sheets and your Android phone, you can Send SMS Messages to your contacts. SMS messages are sent straight from your phone’s SIM card; no third-party SMS provider is necessary.

The Mail Merge for Gmail add-on allows you to send customised emails through Gmail, but wouldn’t it be great if there was a comparable option for sending personalised SMS to your contacts directly from your mobile phone?

There are services, such as Twilio and Vonage, that allow you to send text messages programmatically to any phone number in the globe. You may either develop an SMS solution on top of these messaging APIs, or you can take a simpler and less expensive approach and create your own text-sending app using Google Sheets and MIT’s App Inventor. Also How to Convert Emails into Support Tickets Gmail to Freshdesk

Before we get started, let me show you a brief preview of our text-messaging programme, which allows you to send SMS from any Android phone. If your phone supports international texting, you may send messages to any number in your country as well as foreign phone numbers. You’ll be charged the usual text messaging fees associated with your cellular plan.

Here’s a link to my Google Sheet with the underlying data.

The Google Sheet can contain additional columns for SMS customization, but the three required columns are Phone (for your contact’s phone number), Status (if the SMS was sent to that phone), and Text (the personalised text message).

You may construct the text messages string from different columns using ArrayForumula and simple concatenation, as illustrated below:

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

Now that your source data is available in the sheets, we’ll utilise Google Apps Script to transform it into an API. This would allow our Android app to read the data from the sheets via a simple HTTPS request.

Go to Tools, Script Editor, and put this code inside the sheets.

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
    );
};

Then, in the Google Script Editor, go to the Publish menu and select Deploy as web app. Select “Me” under “Execute the App” and “Anyone, including anonymous” under “Who has access.”

When you click the Deploy button, you’ll be given a secret API URL that you’ll need in the following step. This API URL should NOT be shared with anybody.

Now that our API for Google Sheets is complete, we’ll create an Android app that will read the list of text messages and phone numbers from Google Sheets and send SMS messages. Rather of using a third-party SMS gateway provider, the texts are sent straight from your phone’s SIM card.

Then, in the Google Script Editor, go to the Publish menu and select Deploy as web app. Select “Me” under “Execute the App” and “Anyone, including anonymous” under “Who has access.”

When you click the Deploy button, you’ll be given a secret API URL that you’ll need in the following step. This API URL should NOT be shared with anybody.

Now that our API for Google Sheets is complete, we’ll create an Android app that will read the list of text messages and phone numbers from Google Sheets and send SMS messages. Rather of using a third-party SMS gateway provider, the texts are sent straight from your phone’s SIM card.

Create an SMS App for Android Without Coding

To construct Android applications, you’d generally need to know programming languages like Flutter or Java, but in this lesson, we’ll utilise MIT’s App Inventor, a simple approach to make fully complete apps with drag-and-drop.

Sign in with your Google account to the appinventor.mit.edu website and build a new App. Drag the following components onto your project while in design mode:

• ListView -> For showing the message list retrieved from Google Sheets.
• Button -> For retrieving messages from Google Sheets and sending SMS messages from the Android app.
• Web connectivity is used to make GET and POST requests to Apps Script.
• Notifier, User Interface -> For showing progress bars and alarms.
• Social, Texting -> Used to send SMS texts.

Then, inside App Inventor, navigate to the Blocks area and design the blocks as shown in the video instruction.

We’re almost finished.

Inside App Inventor, go to the Build menu, choose App (supply QR code for.apk), and scan the QR code with your phone. It will download an APK file to your phone, install it, and you will be able to send text messages.