Data Import

I would like for my users to upload data from a csv file to match the columns to my sheets. How would I do that in glide?

Here’s a concept video from a while ago by @Darren_Murphy .

Link: Loom | Free Screen & Video Recording Software | Loom

1 Like

yes, I want that with being able to add the data to the correct columns already aligned in the sheet. While being able to add who uploaded them. Is there a full tutorial on how to do that?

What plan are you on? Are you able to use the Glide API?

Hey, I have been working on something similar within GlideApps.

Take a look at this basic video, where I demonstrate how the whole thing works. If this is what you need, I can help you out and give you step by step instructions of how to create the whole data imports structure.

Here’s the demo video: https://www.loom.com/share/08abd16e50e842fe9560ff7d92af064d?sid=0bebab46-0f27-4b78-9116-2e427c338099

Here is my app script webapp code block:

/**
 * Google Apps Script Web App doPost handler. This function receives HTTP POST requests and processes them.
 * It extracts parameters from the request to use with the Large_GlideApps_Data_Importer function.
 * @param {Object} e The event parameter for doPost, containing incoming HTTP request data.
 * @return {GoogleAppsScript.Content.TextOutput} Returns a JSON response with the status of the data import process.
 */
function doPost(e) {
  try {
    var params = JSON.parse(e.postData.contents);
    var response = Large_GlideApps_Data_Importer(
      params.csv_file_url, 
      params.appID, 
      params.secret_code, 
      params.header_mapping, 
      params.glide_table_name, 
      params.additional_fields
    );
    return ContentService.createTextOutput(JSON.stringify(response))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    console.error("Error parsing JSON: " + error.message);
    return ContentService.createTextOutput("Error parsing JSON: " + error.message)
      .setMimeType(ContentService.MimeType.TEXT);
  }
}

/**
 * Add rows in GlideApps from a csv file using a batch API call to Glide's servers.
 * @param {String} csv_file_url The url within which the CSV file is stored
 * @param {String} appID The app ID of the target GlideApp
 * @param {String} secret_code The 'Bearer *****' secret token provided by Glide for API call authorization
 * @param {Object} header_mapping The json of the header's of the columns of the target table, reversed. Example: {'Company Name ':'dcfynw4t6'}
 * @param {String} glide_table_name The native name provided by Glide for that particular Glide Table.
 * @param {Object} additional_fields Additional fields to be added to each row. Example: {'createdOn': '2023-01-01', 'createdBy': 'user@example.com'}
 * @return {Object} Returns the status of the function run.
 * @customfunction
 */
function Large_GlideApps_Data_Importer(csv_file_url, appID, secret_code, header_mapping, glide_table_name, additional_fields) {
  console.log("Function started");

  if (!csv_file_url || !appID || !secret_code) {
    console.log("Error: Missing required parameters.");
    return { status: 'failure', error: 'Missing required parameters' };
  }

  try {
    console.log("Fetching CSV content");
    var response = UrlFetchApp.fetch(csv_file_url);
    var csvContent = response.getContentText();
    console.log("CSV content fetched");

    console.log("Parsing CSV content");
    var data = Utilities.parseCsv(csvContent);

    if (data.length === 0) {
      console.log("No data found in CSV.");
      return { status: 'failure', error: 'No data found in CSV' };
    }

    console.log("CSV data parsed. Number of rows (including header): " + data.length);
    var headers = data[0];
    console.log("Headers: ", headers);

    var batchSize = 500;
    var allResponses = [];

    console.log("Processing data in batches");
    for (var i = 1; i < data.length; i += batchSize) {
      try {
        var batch = data.slice(i, Math.min(i + batchSize, data.length));
        var mutations = batch.map(row => {
          var columnValues = {};
          headers.forEach((header, index) => {
            var apiField = header_mapping[header];
            if (apiField) {
              columnValues[apiField] = row[index];
            }
          });

          // Add additional fields to each row
          for (var key in additional_fields) {
            if (additional_fields.hasOwnProperty(key)) {
              columnValues[key] = additional_fields[key];
            }
          }

          return {
            "kind": "add-row-to-table",
            "tableName": glide_table_name,
            "columnValues": columnValues
          };
        });

        console.log("Batch ready to send. Number of mutations: " + mutations.length);

        var apiData = JSON.stringify({
          "appID": appID,
          "mutations": mutations
        });

        var options = {
          'method': 'post',
          'contentType': 'application/json',
          'headers': {
            'Authorization': secret_code
          },
          'payload': apiData
        };

        console.log("Sending batch to Glide API");
        var apiResponse = UrlFetchApp.fetch('https://api.glideapp.io/api/function/mutateTables', options);
        console.log("Batch sent. Response: ", apiResponse.getContentText());
        allResponses.push(apiResponse.getContentText());
      } catch (batchError) {
        console.log("Error processing batch from row " + i + ": " + batchError.message);
        allResponses.push({ batchStart: i, error: batchError.message });
      }
    }
  } catch (e) {
    console.log("Error: " + e.message);
    return { status: 'failure', error: e.message };
  }

  console.log("Function completed");
  return { status: 'success', responses: allResponses };
}

Please let me know if what you see in the video, is something that helps you.

Then, I can help you out step by step.

4 Likes

no plan for not but iplan to when i get everything ready

this is very nice. thank you so much. My thing is when someone has the columns different how would they pair to my sheet

Yeah, the method I shared required Glide API, which is only available for Team/Business/Enterprise plans.

The only way to do this would be to match the column headers of the glide table to that of the google sheet. If you can keep a template sheet ready that anyone can view or download, then there should be no problem. You can either give the sheet columns the glide table column headers or the glide table column keys as highlighted below in red.

"ADRS/Can Create Address": "J0QQf",
    "ADRS/Can Read Address": "30f3h",
    "ADRS/Can Update Address": "7WgrO",
    "ADRS/Can Delete Address": "Y1J01"
2 Likes

Hi @Alaap_Kanchwala1, this seems really promising.

1 Like