Upload file example in google app script?

dlicc · January 30, 2024, 5:04am

Hi All,

Ok, next challenge! I’d like to be able to upload a file via the Monday API from google app script (Googles javascript environment). I have basic infrastructure in place in google app script that is working well to create and modify Monday items. (Thanks to all that helped with some of the questions I had figuring this all out over the last few days!)

Looking at the Monday docs, I do see a javascript example that I don’t totally understand for uploading a file to an item. It looks like it uses

require(‘fs’);
require(‘node-fetch’)

which doesn’t exist in google’s javascript environment as fas as I can tell. So, anybody else written a complete app script example of how to upload a file? Any help appreciated!

dlicc · February 1, 2024, 3:40am

Struggling through this by referencing and piecing together what I can. Any thoughts on this so far? compiles at least now without dependencies above that are not available in google app script. Returns {“error_message”:“Unsupported query”,“status_code”:400}.

function testMondayFileUpload() {

  var files = DriveApp.getFilesByName("test.rtf");
  var file = files.next();

  // set filename
  var upfile = 'test.rtf';

  var query = 'mutation ($file: File!) { add_file_to_column (file: $file, item_id: 1234567890, column_id: "files") { id } }';

  var boundary = "xxxxxxxxxx";
  var data = "";


    // construct query part
    data += "--" + boundary + "\r\n";
    data += "Content-Disposition: form-data; name=\"query\"; \r\n";
    data += "Content-Type:application/json\r\n\r\n";
    data += "\r\n" + query + "\r\n";

    // construct file part
    data += "--" + boundary + "\r\n";
    data += "Content-Disposition: form-data; name=\"variables[file]\"; filename=\"" + upfile + "\"\r\n";
    data += "Content-Type:application/octet-stream\r\n\r\n";
    var payload = Utilities.newBlob(data).getBytes()
    .concat(file.getBlob().getBytes())
    .concat(Utilities.newBlob("\r\n--" + boundary + "--").getBytes());


    var response = UrlFetchApp.fetch("https://api.monday.com/v2/file", {
    method: 'post',
    headers: {
      'Content-Type': "multipart/form-data; boundary=" + boundary,
      'Authorization': API_KEY,
      'API-Version' : '2024-01'
    },
    payload: JSON.stringify({
      'body': payload,
    })
  })

  Logger.log(response);

}

dlicc · February 1, 2024, 8:05pm

Also, adding muteHttpExceptions : true does not seem to return anything more useful for debugging…

imjoaofernandes · February 1, 2024, 9:06pm

I am having issues as well when using add_file_to_column… 2 days ago it was working perfectly, suddenly it stopped working and it returns “400 Unsupported query” everytime. If you actually find any solution, I would appreciate you sharing it, I’ll do my best too.

dlicc · February 1, 2024, 9:16pm

are you using javascript? If so can you share your code?

imjoaofernandes · February 1, 2024, 9:31pm

Yeah, a few seconds ago, I was able to make it work using Node.js! It will prompt you for a image path, and for a ID. Here is the complete file, just to make the call and send a simple image to a Monday table:

const express = require('express');
const multer = require('multer');
const FormData = require('form-data');
const fs = require('fs');
const axios = require('axios');
const path = require('path');

const app = express();
const port = 3000;

const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, 'uploads/');
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    const extension = path.extname(file.originalname);
    cb(null, file.fieldname + '-' + uniqueSuffix + extension);
  }
});

const upload = multer({ storage: storage });

app.post('/upload', upload.single('file'), async (req, res) => {
  const itemId = req.body.itemId;
  const filePath = req.file.path;

  let data = new FormData();
  data.append('query', `mutation add_file($file: File!) {add_file_to_column (item_id: ${itemId}, column_id:"arquivos", file: $file) {id}}\n`);
  data.append('map', '{"image":"variables.file"}\n');
  data.append('image', fs.createReadStream(filePath));

  let config = {
    method: 'post',
    url: 'https://api.monday.com/v2/file',
    headers: { 
      'Authorization': 'Bearer YOUR_MONDAY_API_KEY', 
      ...data.getHeaders()
    },
    data: data,
    maxBodyLength: 'Infinity'
  };

  try {
    const response = await axios.request(config);
    console.log("Upload successful:", JSON.stringify(response.data));
    res.json({ message: "Upload successful", data: response.data });
  } catch (error) {
    console.error("Upload failed:", error);
    res.status(500).json({ message: "Upload failed", error: error.toString() });
  }
});

app.listen(port, () => {
  console.log(`Ligado: http://localhost:${port}`);
});

Now, I’m trying to use it on my original API, if I fix it, I’ll say something! But yeah, that code that I just sent you works, just replace it with your key!

imjoaofernandes · February 1, 2024, 9:46pm

Yeah, somehow I was able to fix it, can’t really explain to you why cuz I really don’t know, here is my endpoint and the necessary stuff for it:

import express from 'express';
import fs from 'fs';
import cors from 'cors';
import path from 'path';
import jwt from 'jsonwebtoken';
import lockfile from 'proper-lockfile';
import { expressjwt as expressJwt } from 'express-jwt';
import multer from 'multer';
import fetch from 'node-fetch';
import FormData from 'form-data';

// Additional changes for __dirname and __filename as they are not available in ES module scope
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const uploadsDir = path.join(__dirname, 'uploads');
if (!fs.existsSync(uploadsDir)){
    fs.mkdirSync(uploadsDir, { recursive: true });
}

const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, uploadsDir)
    },
    filename: function (req, file, cb) {
        // Use the MIME type to determine the correct file extension
        const ext = file.mimetype.split('/')[1];
        cb(null, file.fieldname + '-' + Date.now() + '.' + ext);
    }
});

const JWT_SECRET = 'MY_SECRET_JWT';

const app = express();

app.use(cors());
app.use(express.json());
// Initialize multer with the defined storage engine
const upload = multer({ storage: storage });

function logWithTimestamp(...args) {
    const date = new Date();
    const formattedDate = `[${date.getDate()} ${date.toLocaleString('default', { month: 'short' })} ${date.getHours()}:${date.getMinutes()}]`;
    console.log(formattedDate, ...args);
}

app.post('/upload-file', upload.single('file'), async (req, res) => {
    if (!req.file) {
        return res.status(400).send('No file uploaded.');
    }

    // Extract partId from the query string
    const partId = req.query.peca; // Assuming 'peca' is the query parameter for part ID
    if (!partId) {
        return res.status(400).send('Part ID is missing.');
    }

const formData = new FormData();

// Adjust the GraphQL mutation if necessary to match variable names
const mutation = `
    mutation ($file: File!) {
        add_file_to_column (item_id: ${partId}, column_id: "arquivos", file: $file) {
            id
        }
    }
`;

// Add the 'map' field to correctly map the file to the mutation variable
formData.append('query', mutation);
formData.append('map', '{"file":"variables.file"}\n'); // Adjust the key here as needed
formData.append('file', fs.createReadStream(req.file.path), { // Ensure this key matches the 'map'
    filename: req.file.filename,
    contentType: req.file.mimetype,
});

    try {
        const response = await fetch('https://api.monday.com/v2/file', {
            method: 'POST',
            body: formData,
            headers: {
                ...formData.getHeaders(),
                'Authorization': 'Bearer MONDAY_API_TOKEN',
            },
        });

        const result = await response.json();
        if (result.data) {
            logWithTimestamp('File uploaded successfully to Monday.com', result);
            res.send('File uploaded successfully');
        } else {
            logWithTimestamp('Error uploading file to Monday.com', JSON.stringify(result.errors));
            res.status(500).send('Error uploading file to Monday.com');
        }
    } catch (error) {
        logWithTimestamp('Error in file upload', error.message);
        res.status(500).send('Error in file upload');
    } finally {
        // Delete the file after uploading to Monday.com
        fs.unlink(req.file.path, (err) => {
            if (err) logWithTimestamp('Error deleting file:', err);
        });
    }
});

dlicc · February 2, 2024, 1:17am

thanks for posting. I’m not really able to translate most of that to google app script. So still just shooting in the dark here, which no real strategy on how to debug from here…

dlicc · February 2, 2024, 1:27am

Also, just tried using google app script library FetchApp to help with form formatting. But same results

var boundary = "xxxxxxxxxx";
  var query = 'mutation ($file: File!) { add_file_to_column (file: $file, item_id: 5668458954, column_id: "files") { id } }';

  var url = "https://api.monday.com/v2/file";
  var blob = Utilities.newBlob("sample value", MimeType.PLAIN_TEXT, "sample.txt");
  var form = FetchApp.createFormData();
  form.append("query", Utilities.newBlob(query));
  form.append('variables[file]', blob);


  var params = {
    method: "POST",
    headers: {
      'Content-Type': "multipart/form-data; boundary=" + boundary,
      'Authorization': API_KEY,
      'API-Version' : '2024-01'
    },
    body: form,
  };
  var res = FetchApp.fetch(url, params);
  console.log(res.getContentText());

imjoaofernandes · February 2, 2024, 1:29am

Well, if you want, you can leave here a little bit of your code or send it to me, I can try to help you debugging it, but never messed with Google App Scripts Maybe try giving ChatGPT or some free models around a try, maybe some can help you!

dlicc · February 2, 2024, 1:31am

thats a nice offer. The key parts are all above. I believe that query is correct because it seems to be consistent with your example and the others that I have found in the documentation.

I don’t think the body payload is correct. It doesn’t seem like the examples are consistent.

dlicc · February 2, 2024, 1:35am

This seems different from other examples. Where is this coming from?

dlicc · February 2, 2024, 1:56am

Looks like Monday updated their docs on Assets. I updated my code to match. Still does not work. Here is the latest try.

function testMondayFileUpload() {

  var files = DriveApp.getFilesByName("test.txt");
  var file = files.next();
  Logger.log(file.getBlob().getDataAsString());
  // set filename
  var upfile = './test.txt';

  var query = 'mutation ($file: File!) { add_file_to_column (file: $file, item_id: 5668458954, column_id: "files") { id } }';

  var boundary = "xxxxxxxxxx";
  var data = "";
  var map = {"file":"variables.file"};

    // construct query part
    data += "--" + boundary + "\r\n";
    data += "Content-Disposition: form-data; name=\"query\"; \r\n";
    data += "Content-Type:application/json\r\n\r\n";
    data += "\r\n" + query + "\r\n";

        // construct map part
    data += "--" + boundary + "\r\n";
    data += "Content-Disposition: form-data; name=\"map\"; \r\n";
    data += "Content-Type:application/json\r\n\r\n";
    data += "\r\n" + JSON.stringify(map)+ "\r\n";

    data += "--" + boundary + "\r\n";
    data += "Content-Disposition: form-data; name=\"file\"; filename=\"" + upfile + "\"\r\n";
    data += "Content-Type:application/octet-stream\r\n\r\n";

    var payload = Utilities.newBlob(data).getBytes()
    .concat(file.getBlob().getBytes())
    .concat(Utilities.newBlob("\r\n--" + boundary + "--\r\n").getBytes());

Logger.log(payload);

    var response = UrlFetchApp.fetch("https://api.monday.com/v2/file", {
    method: 'post',
    headers: {
      'Content-Type': "multipart/form-data; boundary=" + boundary,
      'Authorization': API_KEY,
      'API-Version' : '2024-01'
    },
    'body': payload
  })

  Logger.log(response);
  var result = JSON.parse(response.getContentText());
  Logger.log(result);

}

imjoaofernandes · February 2, 2024, 12:23pm

Were you able to fix it?
All I know is that I used Postman, because Monday has an official collection to it, where they have the add_file_to_column API call working, and they have that “map” thing included, I guess it’s what they use to map correctly the uploaded file to the file variable on the query!

dlicc · February 3, 2024, 5:11am

No. And the error returned from the post is no help. See my code above with the updated map tag per their updated docs. Still doesn’t work.

I have many other post requests working fine, so this issue is something related to this complicated multi part file upload.

dipro · February 8, 2024, 6:25pm

@dlicc – I wasn’t able to reproduce this “Unsupported query” error, was able to get it working in Postman.

Can you share a few more things so we can check the logs –

When did you try this last?
Your board/item ID
The full response body

If I don’t find anything in the logs I’ll also try running this in AppsScript… But will probably only get to it next week.

dlicc · February 9, 2024, 12:56am

Thanks for helping Dipro.

Its doesn’t seem like muteHttpExceptions : true does anything different…. So all I am getting is this result. Ran again just now, so this is from 4:50:19 PM pacific time today.

Exception: Request failed for https://api.monday.com returned code 400. Truncated server response: {“error_message”:“Unsupported query”,“status_code”:400} (use muteHttpExceptions option to examine full response)

Here is my full test code, based off of the javascript example from the docs:

var files = DriveApp.getFilesByName(“test.txt”);
var file = files.next();
Logger.log(file.getBlob().getDataAsString());
// set filename
var upfile = ‘./test.txt’;

var query = ‘mutation ($file: File!) { add_file_to_column (file: $file, item_id: 5668458954, column_id: “files”) { id } }’;

var boundary = “xxxxxxxxxx”;
var data = “”;
var map = {“file”:“variables.file”};

// construct query part
data += "--" + boundary + "\r\n";
data += "Content-Disposition: form-data; name=\"query\"; \r\n";
data += "Content-Type:application/json\r\n\r\n";
data += "\r\n" + query + "\r\n";

    // construct map part
data += "--" + boundary + "\r\n";
data += "Content-Disposition: form-data; name=\"map\"; \r\n";
data += "Content-Type:application/json\r\n\r\n";
data += "\r\n" + JSON.stringify(map)+ "\r\n";

data += "--" + boundary + "\r\n";
data += "Content-Disposition: form-data; name=\"file\"; filename=\"" + upfile + "\"\r\n";
data += "Content-Type:application/octet-stream\r\n\r\n";

var payload = Utilities.newBlob(data).getBytes()
.concat(file.getBlob().getBytes())
.concat(Utilities.newBlob("\r\n--" + boundary + "--\r\n").getBytes());

Logger.log(payload);

var response = UrlFetchApp.fetch("https://api.monday.com/v2/file", {
method: 'post',
headers: {
  'Content-Type': "multipart/form-data; boundary=" + boundary,
  'Authorization': API_KEY,
  'API-Version' : '2024-01'
},
'body': payload

})

Logger.log(response);
var result = JSON.parse(response.getContentText());
Logger.log(result);

Cheers,
Darren Liccardo
Cofounder & Managing Director
+1.408.564.1601
Catapult Ventures | @dliccardo | @catapultseed

dlicc · February 13, 2024, 3:44pm

@dipro : Any updates here?

dlicc · February 14, 2024, 5:56pm

Any updates here Monday team? I could really use some help getting debug visibility into this issue…

Cheers,
Darren

dipro · February 14, 2024, 6:02pm

@dlicc – couldn’t find anything interesting in our logs, sorry!

I do want to reproduce it in my account though. I haven’t gotten to it yet, I have been working on this big API migration we have. Will try and poke around today.

Topic		Replies	Views
Error response in upload file to comment from API monday apps & integrations files , api , curl	0	554	August 28, 2023
Payload to Debug monday apps & integrations api	0	292	January 11, 2024
Google Sheets API - update all items in monday.com monday apps & integrations integrations , discussion	5	4211	March 10, 2021
API to manage my app monday dev automations , api	1	777	August 9, 2023
Python Package for simpler interface with the API Special workflows & use cases automations , api , python , apps_framework , developer	0	2123	July 12, 2022

Upload file example in google app script?

Become part of our community groups

Can't find what you're looking for?

Can't find
what you need?

Upload file example in google app script?

Related Topics

Become part of our community groups

Can't find what you're looking for?

Can't findwhat you need?

Can't find
what you need?