Use API to Get All Rows from Glide Table to Google Sheet

Ask for Help
1

Hey, I’m new to working with APIs and trying to read through documentation and go through tutorials. I’d like to make sure what I’m trying to do is technically possible.

I have a Glide Table that is storing images. I would like to pull the data from the Glide Table into a Google Sheet so that I can use the Image Key from the Glide Table as validation criteria for the sheets that have columns to reference the images.

Is it possible to use Google Apps Script to use the “Get All Rows” API Instructions provided by Glide when you right click on the Table to do this?

2

Yes, you can manipulate data in Glide Tables via the API using Apps Script.
It’s not as simple as copy/paste though, if that’s what you are asking. You actually need to write a bit of code :wink:

1 Like
3

Yes, I figured out that copy/paste doesn’t work haha. But I just wanted to make sure that my scenario was possible before I spent a lot of time trying to figure out how to get it working. I have a very basic understanding of JavaScript, so I need to spend some time working on it. Thanks!

4

Here is a bit of generic code that should get you started…

function get_all_rows(table) {
  var url = GLIDE_API_URL + 'queryTables';
  var obj = {
    appID: APP_ID,
    queries: [
      {
        tableName: table
      }
    ]
  }
  var payload = JSON.stringify(obj);
  var options = {
    method: 'POST',
    headers: {
      "Authorization": GLIDE_API_KEY,
      "Content-Type": "application/json"
    },
    payload: payload,
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data;
}
1 Like
5

Thanks for the head start. I believe I have the API call working correctly now.

Now I’m trying to pass that data into the Google Sheet. I tried following a YouTube tutorial, but it didn’t quite match up.

I believe this part is working correctly and it gives me a menu option to run the Script:

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Glide')
    .addItem('Display Glide' , 'get_all_rows')  
    .addToUi();
}

However, I’m having trouble with the last part. So far I have this:

let sheet = SpreadsheetApp.getActiveSheet();

let glideRows = [];
glideRows.push(data.name);

let glide = [];
glide.push(glideRows);

let targetRange = sheet.getRange('A2:G');

targetRange.SetValues(glide);

I don’t know if I need to individually reference each column value using .push to insert each one, or if it can be something more generic to simply return everything.

6

That’s probably going to fail because there will be a mis-match between the record count and the number of rows/columns in the range.

Also, it looks like you’re selecting a single attribute from the API result, but selecting 7 columns (A:G) in your spreadsheet. That won’t work.

And as far as I can see, you’re only pushing the first record. I would expect you’d need to iterate through the dataset and push all records.

What you need to do is count the number of records returned from the API, and select a range that corresponds to that. So maybe something like:

var num_rows = data.length;

and then later on…

sheet.getRange(2, 1, num_nows, num_cols).setValues(data);
7

I had only typed out one because I couldn’t figure out the correct naming scheme.

I suppose I don’t actually need the data from all 7 columns, I just didn’t know if I had to pass all of it along. I really only need the data from the first column. The column name is “Name”, but I didn’t know how to properly reference that.

Is it okay to do something like A2:A since I don’t know the exact number of rows, but I’ll always want all of them?

I’ll play around with it some more, but I’ll probably just need to get my friend that knows JavaScript well to help.

Do you know of any good tutorials on the basics of integrating Apps Script with Google Sheets?

I’d like to learn it more in depth eventually, but I don’t have time to learn JavaScript from the ground up at the moment.

8

No, that won’t work. You need to select two-dimensional array (range) that is an exact match for your data. But you can determine that by using data.length.

There probably are, but I none that I can recommend.
Personally, I just taught myself by referring to the docs.

1 Like
9

I got it all working now with some additional help from a friend. Thanks for your help!

1 Like
10

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.