Adding Files to an item from Google Drive using GAS

Hi,

I have a Google Sheet with file names and Monday pulseIDs. I am trying to write a script to cycle through the files and add them in Monday to the specific pulse. I have use the API many times from Google sheets and it works great. I have not been successful with files though.

I have been trying to use my understanding of the typical structure, then reading posts on Monday community about uploading files and looking at other sources how to do similar things with GAS. I have not had luck yet.

Here is what I have so far, it just returns an unsupported query erro:
function testFile(){
var fileName = “Copy of Workspace files metadata (backup) - backup.pdf”;

var url = “https://api.monday.com/v2/file”;
var file = DriveApp.getFilesByName(fileName).next();
var boundary = “xxxxxxxxxx”;
var data = “”;
data += “–” + boundary + “\r\n”;
data += “Content-Disposition: form-data; name=“file”; filename=”" + file.getName() + “”\r\n";
data += “Content-Type:” + file.getMimeType() + “\r\n\r\n”;
var nfile = Utilities.newBlob(data).getBytes()
.concat(file.getBlob().getBytes())
.concat(Utilities.newBlob("\r\n–" + boundary + “–”).getBytes());
var query = ‘mutation($file:File!,$item:Int!){add_file_to_column(file:$file,item_id:$item,column_id:“files”){id}}’
var variables = {
“file”: nfile,
“item”: 839038968
}

var options = {
“method” : “post”,
“headers” : {
“Authorization” : mondayAPIkey,
},
“contentType” : “multipart/form-data; boundary=” + boundary,
“payload” : JSON.stringify({
“query” : query,
“variables” : variables,
}),
“muteHttpExceptions”: true,
};
var res = UrlFetchApp.fetch(url, options).getContentText();

Logger.log(res);
}

Hey @ChrisBuna - could you let us know where you’re trying to execute this script? Also - could you let us know the timestamp for the last time you tried to run it?

-Daniel

Hi Daniel,
Trying to run it from Google Sheets from the scripting tool.
I ran it a few times last night. But I just ran it again to give you a time stamp: 20-11-06 12:29:44:473 AST] Fri Nov 06 12:29:44 GMT-04:00 2020

Thanks @ChrisBuna - I think I see it on the logs. It looks like the variable being sent over to the API isn’t an actual file, but rather the URL to the file on Google Drive.

In order to upload files through the API, you’ll need to send the actual file as a multi-part upload.

Let me know if this helps!

-Daniel

That’s the piece I can’t figure out. How do I get it from Google Drive as a multi-part upload?

That’s what I am trying to figure it out but I can’t get it to work. I am doing my best to interpret other examples doing this to other tools but haven’t figured it out yet. Any suggestions or direction would be greatly appreciated.

It might be useful to break this into a two-part question.

  1. Accessing the file and retrieving the data from inside of it
  2. Uploading the file to Monday.com

For #1, if you haven’t already, the best test I can think of would be creating a Google Doc with “hello world” and developing a routine that prints the text content to the console. It ain’t much, but it’s a start. For #2, try programmatically creating a text file with a test sentence and getting that to upload. This might give you a bit more insight than trying the whole process at one time.

Disclaimer: my typical development strategy is “bash rocks together until I make fire” and is much more haphazard than people with actual programming training. :slight_smile: your mileage may vary.

Thanks Melissa, I appreciate your response but I don’t know how to execute either of the two options…

Chris:

The StackOverflow here should help you getting started with creating files for testing purposes. You will probably wind up using File.getBlob() in the final product - documentation here

I assume you have the API documentation on files bookmarked already, but dipro’s post here has far better detailing than the official documentation (AND EXAMPLES).

Historically I’ve had much better luck using Postman to set up my API queries and then copy/pasting them into GAS and adjusting the formatting somewhat to get around the Google idiosyncrasies…

Another line of inquiry for part #2 in the suggested structure above would be creating a “hello world.txt” file on your desktop and attempting to upload to Monday via Postman… in my experience, the API documentation is not particularly helpful, so you want to be certain your query is structured correctly before you start throwing in new potential for error with porting over files.

This is about as much research as I’m able to do without diving into it for myself, and that won’t happen anytime soon since we’re gearing up for EOY inventory. Best wishes and I hope this helped.

Thanks for your response

@dsilva are you able to provide exactly what the request needs to look like for Monday to accept? What does the file data need to look like? Based on what I am seeing online in other forums for how to do it from Google to Box or Google to Slack I am on the right track but Monday isn’t accepting it. What would be the boundary? what else am I missing?

@ChrisBuna I don’t quite have an example of how the data looks like for monday to request, however we do have a code example of uploading a file the hard way (i.e: uploading from memory instead of an arbitrary reference like a file path). It might point you in the right direction here.

-Daniel