Pull Data from sheets into Area120Tables

[CANA-IT]

Hello,

I am working in Area120Tables.
I have a couple app scripts which work in google sheets to:

1. Calculate Origin Location and Destination into miles;

2. Convert Zip code into Country, State, City, Lat, Long.

I can't figure out how to pull the data from the google sheet and populate the appropriate columns in my Area 120 Table.

Any help would be greatly appreciated.

Maybe @cwlind you could take a shot at it. You seem to be the Genius when it comes to these things.

Kurt

[cwlind]

Hi Kurt,
If you want to share your script, I can take a look.  You might want to post it here also:
https://groups.google.com/g/google-apps-script-community

[CANA-IT]

I was trying to get this to work, but was unable to even get it to do what it was supposed to do. I've searched and searched not trying to recreate the wheel. But here I am reaching out for help.

https://support.google.com/area120-tables/answer/10263946?hl=en

I'm going to search in the group you sent and ask the question in there as well.. In a perfect world, I want to pull the data out of the sheet, not have the sheet update the table, if that makes sense.

[cwlind]

So if I understand correctly, you want to "pull" from the Table, not "push" from the Sheet? 

A Bot using Apps script should be able to do this easily. 
I would create a Stand-alone script. Inside, create a new function, say "retrieveData()". 

function retrieveData() {

  const ss = SpreadsheetApp.openById("Your_SPREADSHEET_ID");

  const ws = ss.getSheetByName("YOUR_SHEET/TAB_NAME");

  const dataRange = ws.getRange("THE_RANGE_FOR_YOUR_DATA");  

  const data = dataRange.getValues();

  return data;

}

This is just a very verbose* way of showing how to pull data from a sheet. (*for educational/learning purposes).

If you want to then format or use the data in Tables, you will have to add the Tables service to your Script so it can access the Tables API. [click the "+" next to Services, and find the Area120Tables API, and toggle it on. In the old Apps Script IDE, this can be found under Resources -> Advanced Services.] (this may be why your script isn't working now--ensure you have the Table API added to the script project).

[CANA-IT]

I think to answer your understanding.

I have a table with columns and the information I want to update the columns on the table is on a sheet

I will give this a try.

Always greatly appreciated.

[AndrewB]

@carlinyuen any thoughts?

[carlinyuen]

Hey folks, in general, you should be able to use the same sample scripts from our Help Center or our Apps Script documentation, which should have working code examples using Apps Script to update rows in Tables.