Exchange data between two Apps Scripts using a webapp

We use Apps Script to increase our productivity when we use google sheets or google docs. But did you know that apps script can communicate with another apps script? We can exchange data between sheets that have their own scripts without having the user in the middle. One way to accomplish that is with the use of apps script web apps. A web app provides a url that users can access via a browser, but the url can also be used by another script.

Before getting into the webapp, let's try to exchange data between two Google Sheets in the following situation: Imagine that you are a business owner and have several agents that work as appointment setters. Each agent has their own Google Sheet where they record upcoming appointments. You want to consolidate all appointments into your own manager's sheet. Currently, the agents email you their sheets, but you want to automate the agent sheet to feed the data into your sheet directly. You might try something like this:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('🟢 Run');
  menu.addItem('Send appointments', 'sendAppointments');
  menu.addToUi();
}

function sendAppointments() {
  const managerSpreadsheetId = 'manager-spreadsheet-id';
  const managerSS = SpreadsheetApp.openById(managerSpreadsheetId);
  const logSS = managerSS.getSheetByName('Log');
  const newRow = logSS.getLastRow() + 1;
  const agentSS = SpreadsheetApp.getActive();
  const currentSH = currentSS.getSheetByName('Current');
  const appointments = currentSH
    .getRange(2, 1, currentSH.getLastRow() - 1, currentSH.getLastColumn())
    .getValues();
  logSS
    .getRange(newRow, 1, appointments.length, appointments[0].length)
    .setValues(appointments);
  SpreadsheetApp.flush();
}

Save the above code in the agent's sheet's App Script. onOpen will add a custom menu to execute sendAppointments, which takes the data from the agent's sheet and appends it to the manager's sheet.

When I try to run the code using a different Google account than the manager's sheet, I get an error message: "Exception: You do not have permission to access the requested document." Why is this happening?

The reason for this error is that as a manager, I haven't shared my manager's spreadsheet with the agents, and for good reason: I don't want them to view each other's appointments. So without Editor's access to the manager's sheet, the agent's script is not permitted to access the spreadsheet. So how do I fix this? How can I have the agent update the manager's sheet without direct access? Enter the Apps Script webapp!

The Apps Script webapp behaves similarly to any webapp: the webapp offers a web URL that can be accessed online. It responds to GET and POST requests, as long as it has functions defined to do so. The URL can be accessed by both humans who can view the webapp content in a browser, and by another Apps Script.

In our case, the webapp will be bound to the manager's spreadsheet. It will receive the appointments information using a POST request from the agent. It will take the information and append it to the manager's spreadsheet. It will then acknowledge the receipt, so that the agent script can now that the data arrived successfully.

To get going with the webapp, open the manager's spreadsheet and access Apps Script. Add the following code:

function doPost(e) {
  // Pull the appointments data string from the request body and parse it              
  const appointments = JSON.parse(e.postData.contents);
  // Get the manager's spreadsheet
  const ss = SpreadsheetApp.getActive();
  // Get the Log tab
  const sh = managerSS.getSheetByName('Log');
  // Set the entry point in the log
  const newRow = sh.getLastRow() + 1;
  // Append the data
  sh
    .getRange(newRow, 1, appointments.length, appointments[0].length)
    .setValues(appointments);
  SpreadsheetApp.flush();
  // Send a response that the data has been received
  return ContentService.createTextOutput('received').setMimeType(
    ContentService.MimeType.TEXT
  );
}

The code above defines the function that Apps Script will run automatically whenever a POST request is received. We now need to deploy the manager's Apps Script as a web app. To do so:

• Click the blue Deploy button
• Click the gear icon on the left side
• Select: Web app
• Add a description
• Under "Execute as", select: "Me"
• Under "Who has access," select: "Anyone within your-org-name". This is the safest option, but you can also select "Anyone" just to test this script
• Click Deploy

On the next page, you will be provided with a web app URL. Copy it: We will use it in the agent's script.

Back in the agent's script, modify the script to be as follows:

function sendAppointments() {
  const ss = SpreadsheetApp.getActive();
  const sh = agentSS.getSheetByName('Current');
  const appointments = currentSH
    .getRange(2, 1, currentSH.getLastRow() - 1, currentSH.getLastColumn())
    .getValues();
  const url = 'your-web-app-url';
  const options = {
    method: 'post',
    muteHttpExceptions: true,
    payload: JSON.stringify(appointments),
  };
  const resp = UrlFetchApp.fetch(url, options);
  console.log(resp.getContentText());
}

In the above code you can see that we're still getting the data from the agent's sheet, but this time we create an options object with a POST method, and a payload with the stringified appointments array. We use UrlFetchApp to fetch the URL of the webapp with our options object. We get a response and log it.

If you run sendAppointments from the IDE then you should see the text "received" showing up in the log.

I hope that this gives you an idea about how to exchange data between two Apps Scripts.