Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

utilize Google Scripts to update Airtable fields

Topic Labels: Automations
704 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Eddie_Kaeser
6 - Interface Innovator
6 - Interface Innovator

I want to update my Airtable attachment field with a pdf that is loaded in a Google Drive folder. This is what I have so far, and I think I am almost there. There seems to be an issue (Request failed for https://api.airtable.com returned code 404. Truncated server response: {"error":"NOT_FOUND"}) in the final step (attachPDFToRecord), which I cannot figure out. Any assistance would be appreciated.

 

-----------------------------

function getPDFFilesFromDrive() {
  var folderId = "DRIVE FOLDER ID";
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByType("application/pdf");
  return files;
}

function getAirTableRecords() {
  var apiKey = "APIKEY"; // Replace with your AirTable API key
  var baseId = "BASEID"; // Replace with your AirTable base ID
  var tableName = "TABLEID"; // Replace with your AirTable table name
  var tableUrl = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    Authorization: "Bearer " + apiKey,
  };
  var response = UrlFetchApp.fetch(tableUrl, { headers: headers });
  var records = JSON.parse(response.getContentText()).records;
  return records;
}

function matchPDFsWithRecords() {
  var pdfFiles = getPDFFilesFromDrive();
  var records = getAirTableRecords();
 
  for (var i = 0; i < pdfFiles.length; i++) {
    var pdfName = pdfFiles[i].getName();
    var storeNumber = pdfName.substr(0, 4);
   
    for (var j = 0; j < records.length; j++) {
      var record = records[j];
      if (record.fields["Store#"] === storeNumber) {
        attachPDFToRecord(record.id, pdfFiles[i].getId());
        break;
      }
    }
  }
}

function attachPDFToRecord(recordId, pdfFileId) {
  var apiKey = "APIKEY"; // Replace with your AirTable API key
  var attachmentField = "IG Attachments (NEW)"; // Replace with your correct field name
  var baseId = "BASEID"; // Replace with your AirTable base ID
  var tableName = "TABLEID"; // Replace with your AirTable table name
  var attachmentUrl = "https://api.airtable.com/v0/" + baseId + "/" + tableName + "/" + recordId; // Construct the attachment URL
 
  var headers = {
    Authorization: "Bearer " + apiKey,
    "Content-Type": "application/json",
  };
 
  var payload = {
    fields: {
      [attachmentField]: [
        {
          url: "https://drive.google.com/uc?id=" + pdfFileId,
        },
      ],
    },
  };
 
  var options = {
    method: "PATCH",
    headers: headers,
    payload: JSON.stringify(payload),
  };
 
  UrlFetchApp.fetch(attachmentUrl, options);
}


-----------------------------

1 Reply 1
Ron_Williams
6 - Interface Innovator
6 - Interface Innovator
Remove the record ID from the url and try:
var payload = {
"records": [{
"id": recordId,
"fields": {
      [attachmentField]: [
        {
          "url": "https://drive.google.com/uc?id=" + pdfFileId,
        },
      ],
    },
  }]}
 
The attachment field may also need to be in quotes Im not 100% sure...also:
 
var options = {
    method: "PATCH",
    headers: headers,
    body: JSON.stringify(payload),
  };
 
I haven't tried "payload" in the options but I know "body" works for me. Hope this helps.