Unfortunately, I'm running into an error when attempting to implement this. The result is an unequal amount of header rows compared to the data set, which is quite perplexing given that the values are coming from the same source that is populating the header rows (field_data pushing to sheet_data, which is providing the amount of columns in the range constant). I have 55 as the result for my headers; but 106 for my data set. Any idea what might be going on here? Is it possible to have variance between the "field" and the "fields" based upon the base's design?
@@BootstrappingTools sure, I can paste my version of the importAirTableData here for review: function importAirTableData() { let data = getAllRecords(); let sheet_data = [] for(let item in data) { const field_data = []; if(item == 0) { field_data.push('airtable_id') } else { field_data.push(data[item].id) } let fields = data[item].fields; for(let field in fields) { if(item == 0) { field_data.push(field) } else { field_data.push(fields[field]) } } // console.log(`field_data: ${field_data}`) sheet_data.push(field_data) } const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName('Sheet1'); const range = sheet.getRange(1, 1, sheet_data.length, sheet_data[0].length); range.clear(); range.setValues(sheet_data); }
That does look correct overall... The only thing I could think of is that in some of your airtable columns, the value is blank. Airtable has a nasty habit of omitting the field from the api response if it's completely null. A way around it is to enter in a value - even if it's just a space. The other option would be to list out all of the individual field names you care about and setting a default value to each. Airtable also offers a Metadata API which should give you all of the fields regardless of the data availability, but you need to apply for it. But even if you have this, you would still need to assign default values for each one. The registration form for the Metadata API is located here: airtable.com/shrWl6yu8cI8C5Dh3 I'll ponder on this a bit to see if there's maybe another way.
@@BootstrappingTools Hey, I have a suggestion - not sure if its substantial enough but: I've run into an issue where the base i'm working with has a few multi-select fields. Unfortunately, I don't know how to loop through those arrays to extract all the values. Perhaps it's something you might want to dig into Edit: to clarify, I'm seeing all the array values when debugging the script, which is pushing them into the "sheet_data" variable. However, when it is moved to the sheet, it only returns the first value in the array Edit 2: I figured out a solution...I turned the arrays into strings, which then allowed for them to pass to the sheet. I used a for loop for the multi-select columns and used the .toString() method and then used .setValue() to push them to the sheet (.setValues() won't work with strings).
I do however, have a question. I am getting this Exception: The number of columns in the data does not match the number of columns in the range. The data has 29 but the range has 27. Could this be related to custom fields? (The original table actually has more than 59 columns. Should I be pointing to a particular view ?)
I went and moved the field_data array, and now I have 1-1 correspondence among tables, but received the following error: The JavaScript runtime exited unexpectedly. Downloading too much information? Why the array was not working correctly inside the for loop?
Hi Francisco! I saw that you viewed the pagination video (th-cam.com/video/6fOuDxcbjyg/w-d-xo.html) which should have helped you get over these hurdles. Are you still running into any issues?
@@BootstrappingTools Actually yes. I still get different amount of headers and fields. And this produce an exception. I will appreciate any suggestion. The code is literally the same, nothing was changed.
amazing video! I have implemented this and different of your scripts. Angel!
Unfortunately, I'm running into an error when attempting to implement this. The result is an unequal amount of header rows compared to the data set, which is quite perplexing given that the values are coming from the same source that is populating the header rows (field_data pushing to sheet_data, which is providing the amount of columns in the range constant). I have 55 as the result for my headers; but 106 for my data set. Any idea what might be going on here? Is it possible to have variance between the "field" and the "fields" based upon the base's design?
Hmm, I would have to take a look at your code to see where the problem might be. Would you be okay with pasting it here?
@@BootstrappingTools sure, I can paste my version of the importAirTableData here for review:
function importAirTableData() {
let data = getAllRecords();
let sheet_data = []
for(let item in data) {
const field_data = [];
if(item == 0) {
field_data.push('airtable_id')
} else {
field_data.push(data[item].id)
}
let fields = data[item].fields;
for(let field in fields) {
if(item == 0) {
field_data.push(field)
} else {
field_data.push(fields[field])
}
}
// console.log(`field_data: ${field_data}`)
sheet_data.push(field_data)
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Sheet1');
const range = sheet.getRange(1, 1, sheet_data.length, sheet_data[0].length);
range.clear();
range.setValues(sheet_data);
}
That does look correct overall...
The only thing I could think of is that in some of your airtable columns, the value is blank. Airtable has a nasty habit of omitting the field from the api response if it's completely null. A way around it is to enter in a value - even if it's just a space. The other option would be to list out all of the individual field names you care about and setting a default value to each.
Airtable also offers a Metadata API which should give you all of the fields regardless of the data availability, but you need to apply for it. But even if you have this, you would still need to assign default values for each one. The registration form for the Metadata API is located here: airtable.com/shrWl6yu8cI8C5Dh3
I'll ponder on this a bit to see if there's maybe another way.
@@BootstrappingTools That all sounds right to me. Thank you again for your responsiveness and the resources. It's greatly appreciated.
@@BootstrappingTools Hey, I have a suggestion - not sure if its substantial enough but: I've run into an issue where the base i'm working with has a few multi-select fields. Unfortunately, I don't know how to loop through those arrays to extract all the values. Perhaps it's something you might want to dig into
Edit: to clarify, I'm seeing all the array values when debugging the script, which is pushing them into the "sheet_data" variable. However, when it is moved to the sheet, it only returns the first value in the array
Edit 2: I figured out a solution...I turned the arrays into strings, which then allowed for them to pass to the sheet. I used a for loop for the multi-select columns and used the .toString() method and then used .setValue() to push them to the sheet (.setValues() won't work with strings).
Hi, do you have any videos on how to connect HubSpot with Google Sheets via the API? It's throwing me an error :(
Yeah, I've used the hubspot API before. No videos on it though.
What kind of error are you getting?
@@BootstrappingTools On the sheets I see the fild as null, like this {email=null}
I am pretty sure that I am doing something wrong
How did you manage to get 9994 records from AirTable? I am only getting 100 out of a similar amount. Thanks.
th-cam.com/video/6fOuDxcbjyg/w-d-xo.html Got the answer!!!!!
I do however, have a question. I am getting this Exception: The number of columns in the data does not match the number of columns in the range. The data has 29 but the range has 27. Could this be related to custom fields? (The original table actually has more than 59 columns. Should I be pointing to a particular view ?)
I went and moved the field_data array, and now I have 1-1 correspondence among tables, but received the following error: The JavaScript runtime exited unexpectedly. Downloading too much information? Why the array was not working correctly inside the for loop?
Hi Francisco!
I saw that you viewed the pagination video (th-cam.com/video/6fOuDxcbjyg/w-d-xo.html) which should have helped you get over these hurdles.
Are you still running into any issues?
@@BootstrappingTools Actually yes. I still get different amount of headers and fields. And this produce an exception. I will appreciate any suggestion. The code is literally the same, nothing was changed.