Tutorial: Bulk Adding Records in AppSheet with Google Apps Script

In this tutorial, we will walk through a process of bulk-adding records from one table to another in AppSheet, using Google Apps Script for automation. This is particularly useful when you want to add multiple records at once without manual entry.

Step 1: Understand the Tables

We have three tables in our AppSheet app:

1. Cajas: This is the main table where we want to add records. It has a subtable called “Numeros de Serie 2”.
2. Numeros de Serie 2: This is a subtable of “Cajas”. We want to bulk add records to this table.
3. Serial Numbers: This is the table from which we want to add records to “Numeros de Serie 2”.

Step 2: Create a Slice

We create a slice named “Piezas Disponibles Para Envio” from the “Serial Numbers” table. This slice filters the “Serial Numbers” table to show only the records not already present in the “Numeros de Serie 2” table.

Step 3: Create an EnumList Field

In the “BulkAddRequest” table, we create an EnumList field named “SelectedSerialNumbers”. This field allows us to select multiple serial numbers at once from the “Piezas Disponibles Para Envio” slice.

Step 4: Create an Action

We create an action in the “Cajas” table that uses the LINKTOFORM() function to navigate to the form view of the “BulkAddRequest” table. This action pre-fills the “CajasID” field with the ID of the current “Cajas” record.

Step 5: Trigger Google Apps Script

When the form is submitted, it triggers a Google Apps Script that copies the selected serial numbers from the “BulkAddRequest” table to the “Numeros de Serie 2” table. Each serial number is added as a new record with a unique 8-character alphanumeric ID and the current date and time.

function copyRecords(requestID) {
  // Get the spreadsheet ID from the script properties
  var scriptProperties = PropertiesService.getScriptProperties();
  var spreadsheetId = scriptProperties.getProperty('tablaEnvios');

  // Open the spreadsheet
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);

  // Get the "BulkAddRequest" and "numeros de serie" sheets
  var bulkAddRequestSheet = spreadsheet.getSheetByName("BulkAddRequest");
  var numerosDeSerieSheet = spreadsheet.getSheetByName("numeros de serie");

  // Get the row with the specified request ID
  var requestRow = bulkAddRequestSheet.createTextFinder(requestID).findNext().getRow();

  // Get the values from the "CajasID" and "SelectedSerialNumbers" columns
  var cajasID = bulkAddRequestSheet.getRange(requestRow, 2).getValue();
  var selectedSerialNumbers = bulkAddRequestSheet.getRange(requestRow, 3).getValue().split(',');

  // For each selected serial number, add a new row to the "numeros de serie" sheet
  for (var i = 0; i <selectedSerialNumbers.length; i++) {
    var serialNumber = selectedSerialNumbers[i].trim();

    // Generate a unique 8-character alphanumeric ID
    var uniqueId = generateUniqueId();

    // Get the current date and time
    var now = new Date();

    // Add the new row
    numerosDeSerieSheet.appendRow([now, uniqueId, cajasID, serialNumber]);
  }
}

function generateUniqueId() {
  var length = 8;
  var result = '';
  var characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  var charactersLength = characters.length;
  for (var i = 0; i < length; i++) {
    result += characters.charAt(Math.floor(Math.random() * charactersLength));
  }
  return result;
}

And that’s it! With this setup, your employees can now bulk add records from the “Serial Numbers” table to the “Numeros de Serie 2” subtable of the “Cajas” table, all within the AppSheet app, without having to interact with the underlying Google Sheets directly.

Remember to always test your changes to ensure they work as expected. If you encounter any issues or have any questions, don’t hesitate to ask for help. Happy app building!