AppsScriptPulse

Solutions, Sheets, Intermediate

Automate creating or updating tables in Google Sheets with Apps Script🚀

Tags:
| | 0 Comments |

A step-by-step guide to dynamically import, filter, and format your data.

How many times have you found yourself copying data from one sheet to another, applying filters, and then manually formatting everything as a table to make it more readable and functional? It is a sometimes tedious and error-prone process.

What if we could automate all of this with a single click? Welcome to the world of Google Apps Script!

In this article, we will analyze a script that not only imports and filters data between two spreadsheets but also uses the power of the Sheets API to dynamically create a formatted table. Ready? Let’s start! 🚀

What does the script do?

In summary, our script performs these steps in sequence:

  • Connects to a source spreadsheet (even one different from where the script is located).
  • Imports all the data from the specified sheet.
  • Filters the rows based on a specific condition (in our case, a numeric value in a column).
  • Clears the destination sheet and writes the filtered data to it.
  • Creates (or recreates) a formatted table that perfectly adapts to the size of the new data.

The Sample File

To follow this tutorial, you can create a copy of the sample spreadsheet:

➡️ Click here to create a copy of the sample file

The file contains the source sheet DataSet with the starting data. In your destination sheet, you can, for example, use a sheet named Filtered where the filtered data will be inserted. Nothing prevents you from using this script to filter and create a table in the same file. In any case, for my example, you will need the file I linked and a second file where you import this data.

In the script we will analyze, the filter is applied to the “Billing” column, selecting only rows with a value greater than 2000.

✨Extra: If you want to learn how to use tables:

Preparation: Enable the Sheets API

Before starting, there is a fundamental step. Our script uses advanced methods that are not part of the standard SpreadsheetApp service, but of the advanced Sheets API service.

To enable it:

  • Open the Apps Script editor.
  • In the left menu, click on Services (+).
  • Find Google Sheets API in the list, select it, and click Add.

Perfect! Now your script has the “superpowers” to manipulate tables, conditional formatting, and much more.

Script Analysis

Here is the complete script that you will need to copy and paste into your file:

function importFilterCreateTable() {
    const idOrigin = "ID_FILE"; // Source File ID
    const nameOrigin = "DataSet"; // source sheet name
    const nameDest = "Filtered" // destination sheet name
    const indexCol = "Billing" // name of Column to filter
    const nameTable = "TBL_Filtered" // name of the table

    const ss = SpreadsheetApp.getActive();
    const sheetOrigin = SpreadsheetApp.openById(idOrigin).getSheetByName(nameOrigin);
    const sheetDest = ss.getSheetByName(nameDest);
    const destID = sheetDest.getSheetId();

    // Import & Filter the data
    const values = sheetOrigin.getDataRange().getValues();

    const col = values[0].indexOf(indexCol); // Find the column number to filter

    const filterValues = values.filter((rw, h) => h === 0 || rw[col] > 2000); //Filter by condition

    if (!filterValues.length) return;

    // Retrieve the table data via the Sheets API
    const ssObj = Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false });
    const sheetObj = ssObj.sheets.find(s => s.properties.sheetId === destID);

    const requests = [];

    // Checks if the table exists, if so it deletes it
    if (sheetObj.tables) {
        const table = sheetObj.tables[0];
        requests.push({ deleteTable: { tableId: table.tableId } });
    }

    // Adds the new data
    sheetDest.getRange(1, 1, filterValues.length, filterValues[0].length).setValues(filterValues);

    // Adds a new table
    requests.push({
        addTable: {
            table: {
                name: nameTable,
                range: {
                    sheetId: destID,
                    startRowIndex: 0,
                    endRowIndex: filterValues.length,
                    startColumnIndex: 0,
                    endColumnIndex: filterValues[0].length
                }
            }
        }
    });

    if (requests.length) {
        Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
    }
}

💪🏻Let’s analyze the interesting part about the table.

The Creation of the Table

This is the most interesting and powerful part of the script. The standard SpreadsheetApp service does not have a direct method like sheet.createTable(). To do this, we need to speak directly with the Google Sheets API.

How does it work?

  • Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false }); With this line, we ask the API to provide us with all the properties of our spreadsheet (like the sheets within it, their IDs, and of course, the existing tables), but without downloading the cell data (includeGridData: false), making the call much faster.
  • ssObj.sheets.find(s => s.properties.sheetId === destID); From the object we have received, we look for the specific sheet we are working on by comparing its ID (destID).
  • Preventive deletion: if (sheetObj.tables) Our script is designed to be run multiple times. For simplicity, we delete the old table before creating a new one. How? This block of code checks if a table already exists in the sheetObj sheet (sheetObj.tables). If so, it adds a deletion request (deleteTable) to an array of “requests” (requests).
  • Creation: requests.push({ addTable: { ... } }); We add a second request to our array: the creation of the new table (addTable). Here we define all its properties:
    • name: The unique name of the table.
    • range: The cell range it will occupy. It is crucial to note that the API uses “zero-based” indexes (the first row/column is 0) and the final index (endRowIndex) is excluded. This is why filterValues.length works perfectly to define the last row.

📌 In this example, the table will be placed in cell A1. If you want the table to appear in a different row, for example in row 3 of a column, you must set startRowIndex: 2 and endRowIndex: filterValues.length + 2. If you want it in a different column instead, for example in column D, you will have to put startColumnIndex: 3 and endColumnIndex: filterValues[0].length + 3.

  • Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId()); This is the final call. Instead of making one API call to delete and another to create, we group them into a single batchUpdate. It is more efficient and ensures that the operations are performed in sequence.

This “delete and recreate” approach is extremely robust and ensures that the table always has the exact dimensions of the filtered data. The previous data? It will be removed when we delete the table. In fact, it is only after possibly deleting the table that the new data will be inserted.

Updating an Existing Table

What if we just wanted to update the dimensions of an existing table instead of deleting and recreating it? In this case, the import and filter logic remains the same, but the table manipulation part changes.

This method becomes useful when your table already has a structure that you want to preserve (special formatting, data validation, drop-down menus, etc.) and you just want to update its data and dimensions.

Here is what the final part of the script would look like in this case:

    // Delete the values
    sheetDest.getRange(2, 1, sheetDest.getMaxRows(), sheetDest.getMaxColumns()).clearContent()

    // Insert new values
    sheetDest.getRange(2, 1, filterValues.length, filterValues[0].length).setValues(filterValues);

    // Retrieve the table data via the Sheets API
    const obj = Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false });
    const sheetObj = obj.sheets.find(s => s.properties.sheetId === destID)

    const tableID = sheetObj.tables[0].tableId

    const requests = [{
        updateTable: {
            table: {
                tableId: tableID,
                range: {
                    sheetId: destID,
                    startRowIndex: 0,
                    endRowIndex: sheetDest.getLastRow(),
                    startColumnIndex: 0,
                    endColumnIndex: sheetDest.getLastColumn()
                }
            },
            fields: "range"
        }
    }];

    Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());

What changes?

  • Instead of deleting and creating, we use an updateTable request.
  • We must specify the ID of the table to be modified (tableId).
  • The fields: "range" field is crucial: it tells the API to update only and exclusively the range property of the table, leaving everything else (like the name) unchanged.

Conclusion

Which method to choose? It always depends on the use you want to make of it, so it depends a lot on your needs. Remembering that the “delete and recreate” method of the first script is generally used if we always want to have a new and clean table, while the second case is useful when we have already structured our table.

Automating tasks in Google Sheets with Apps Script can save you hours of work. Learning to use the Sheets API opens up a world of possibilities that go far beyond simple data manipulation, allowing you to programmatically control almost every aspect of your spreadsheets.

Solutions, Drive, Sheets, Beginner

Exporting and importing data between Google Sheets with Google Apps Script

Tags:
| | 2 Comments |

When working with Google Sheets, you often use formulas like IMPORTRANGE to transfer data between files. However, IMPORTRANGE is not always reliable, especially when files become too large and you see the message “The file is too large.” In such cases, switching to a Google Apps Script-based solution that uses CSV files for data export and import is more efficient and flexible.

Here’s how you can create a system to export data from a Google Sheet to a CSV file and then import it into another sheet.

Export Data Script

This script exports data from a sheet named YOUR_TAB_NAME to a CSV file saved in Google Drive.

function exportToCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";

const ss = SpreadsheetApp.getActive();
const sourceSheet = ss.getSheetByName(SheetName);

  // Get the values ​​as displayed in the sheet
  const dataValues = sourceSheet.getDataRange().getDisplayValues();

  // Creating CSV content
  const csvContent = dataValues.map(row => row.join(";")).join("\n");

  // Check if there is data
  if(csvContent == "") return;

  try {
  
  const fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
        
        DriveApp.getFileById(fileId).setContent(csvContent);
  
  } catch {

        DriveApp.createFile(NameFile + ".csv", csvContent, MimeType.CSV);

  }
}

How It Works

  1. Fetches data from the specified sheet (YOUR_TAB_NAME).
  2. Creates CSV content, joining data with the ; separator.
  3. Updates the CSV file if it already exists or creates a new one.

Import Data Script

This script imports data from a CSV file into a Google Sheet named YOUR_TAB_NAME.

function importFromCSV() {
  const SheetName = "YOUR_TAB_NAME";
  const NameFile = "YOUR_FILE_NAME";
  const Separator = ";"; // Change your separator if needed

  const destinationSheet = SpreadsheetApp.getActive().getSheetByName(SheetName);

  let fileId;

  try {
    // Search for the file and get its ID
    fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
  } catch {
    Logger.log("Il file '" + NameFile + ".csv' non è stato trovato in Google Drive.");
    return;
  }

  const file = DriveApp.getFileById(fileId);
  const property = PropertiesService.getDocumentProperties();

  // Check if the last imported data has already been loaded
  const lastModified = property.getProperty("lastModified");

  const timeStamp = file.getLastUpdated().toUTCString();
  property.setProperty("lastModified", timeStamp);

  if (lastModified == timeStamp) return;

  // Retrieve the CSV content
  const csvContent = file.getBlob().getDataAsString();

  // Split the content into rows and then into columns using the specified separator
  const data = csvContent
          .split("\n") // Split into rows
              .map(row => row.split(Separator)); // Split each row into columns using the specified separator

  destinationSheet.clearContents();
  destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

How It Works

  1. Finds the CSV file in Google Drive.
  2. Checks for changes by comparing the file’s last modification time with a stored property value.
  3. Reads data from the CSV file and imports it into the sheet, clearing existing data first.

Automation with Triggers

To automate the import process, you can use a time-based trigger to run the script at regular intervals (e.g., every minute, hour, etc.).

Setting Up a Trigger

  1. Go to Apps Script Editor (Extensions > Apps Script).
  2. Click on Triggers (Clock icon) or Tools > Triggers in the editor.
  3. Create a new trigger:
    • Choose the importFromCSV function.
    • Select “Time-driven trigger.”
    • Specify the frequency (e.g., every minute).

With this trigger, your script will regularly check for updates in the CSV file and automatically import new data.

Explanation of the Separator Usage

Why the semicolon (;) is used: Descriptions or text fields may already use commas (,), and using them as a separator could lead to incorrect data splits. Using semicolons avoids this issue.

Alternative separator: If semicolons (;) are also present in the data, it’s recommended to use a unique symbol, such as §.

To update the separator, replace “;” with “§” in the following line:

.map(row => row.split("§")); // Update the separator here

This makes the script adaptable to various data scenarios.

Why Use This Method?

Advantages

  • Avoid IMPORTRANGE Limits: No errors related to overly large files.
  • Efficiency: Data is transferred as CSV, reducing connectivity issues between files.
  • Automation: Imports happen without manual intervention.

Limitations

  • Maintenance: Scripts need to be managed and updated manually if changes occur.
  • Security: Ensure file and script access is secure.

With these scripts and a configured trigger, you can reliably and efficiently handle large volumes of data between Google Sheets. If you need further customizations or help setting it up, feel free to ask!