Monday, 25 January 2021

Patch request from google sheets overriding previous post request

I am sending 12 cell values from a google sheet to a mongodb database. The reason I'm doing it is because I want to concatenate the 12 cells and do some transforms on the data and output it on a front end somewhere after. I'm also doing this because sheets limit each cell to 50k characters and I have around 500k characters I need to POST to the database each time. My initial assumption here is that I need to create 1 record with all 12 cell values in MondoDB, ( potentially a different avenue would be to just post 12 separate records in a collection ). So the way I'm doing it is doing a POST request, and then potentially 11 PATCH requests on the initially posted record all in one google scripts function inside google sheets. The problem I'm having is that I'm not sure how to form the model & route & request. Everytime I do a patch (shown below) it overrides the previous POST data. I want it to only update the part of the JSON that is being sent via the PATCH request. Currently the PATCH request is changing every other record to null and deleting the previous POSTs data. I know there is a way to patch only a specific record in a collection, but how to patch a specific part of the json in a collection I don't get.

EXPRESS POST ROUTE:

router.post('/', async (req,res) => {
  const post = new Post({
    title: req.body.title,
    en1: req.body.en1,
    en2: req.body.en2,
    en3: req.body.en3,
    en4: req.body.en4,
    fr1: req.body.fr1,
    fr2: req.body.fr2,
    fr3: req.body.fr3,
    fr4: req.body.fr4,
    de1: req.body.de1,
    de2: req.body.de2,
    de3: req.body.de3,
    de4: req.body.de4
  });

  try {
    const savedPost = await post.save();
    res.json(savedPost);
  } catch (err) {
    res.json({ message: err })
  }
})

EXPRESS UPDATE ROUTE:

router.patch('/:postId', async (req,res) => {
  try {
    const updatedPost = await Post.updateOne(
      { title:req.params.postId }, 
      { $set: {
        title: req.body.title,
        en1: req.body.en1,
        en2: req.body.en2,
        en3: req.body.en3,
        en4: req.body.en4,
        fr1: req.body.fr1,
        fr2: req.body.fr2,
        fr3: req.body.fr3,
        fr4: req.body.fr4,
        de1: req.body.de1,
        de2: req.body.de2,
        de3: req.body.de3,
        de4: req.body.de4 
      } }
    )
    res.json(updatedPost)
  }catch(err){
    res.json({ message: err })
  }
})

MONGOOSE MODEL:

const mongoose = require('mongoose')

const PostSchema = mongoose.Schema({
  title: {
    type: String,
    required: false
  },
  en1: {
    type: String,
    required: false
  },
  en2: {
    type: String,
    required: false
  },
  en3: {
    type: String,
    required: false
  },
  en4: {
    type: String,
    required: false
  },
  fr1: {
    type: String,
    required: false
  },
  fr2: {
    type: String,
    required: false
  },
  fr3: {
    type: String,
    required: false
  },
  fr4: {
    type: String,
    required: false
  },
  de1: {
    type: String,
    required: false
  },
  de2: {
    type: String,
    required: false
  },
  de3: {
    type: String,
    required: false
  },
  de4: {
    type: String,
    required: false
  },
  date: {
    type: Date,
    default: Date.now
  }
})

module.exports = mongoose.model('Posts', PostSchema)

GOOGLE SCRIPT POST REQUEST FROM SHEETS (with only one patch request for now):

function sendInfoToApi() {
  const randomId = Math.random()*100000000000000000;

  var en1 = SpreadsheetApp.getActive().getSheetByName("Final").getRange('A3').getValues()[0][0];
  var en2 = SpreadsheetApp.getActive().getSheetByName("Final").getRange('A4').getValues()[0][0];

  // 11111 POST REQUEST //
  var data1 = {
    "title": randomId,
    "en1": en1
  }
  var payload1 = JSON.stringify(data1)  
  var url1 = 'https://ag-sheets-api.herokuapp.com/posts';
  var fetchParameters1 = {
      'method': 'post',
      'contentType': 'application/json', 
      'payload' : payload1,
      'muteHttpExceptions' : false
  };
  try {
      var response = UrlFetchApp.fetch(url1, fetchParameters1);
      } catch(e){
        Logger.log(e)
  }

  // 22222 PATCH REQUEST //
  var data2 = {
    "title": randomId,
    "en2": en2
  }
  var payload2 = JSON.stringify(data2)  
  var url2 = `https://ag-sheets-api.herokuapp.com/posts/${randomId}`;
  var fetchParameters2 = {
      'method': 'patch',
      'contentType': 'application/json', 
      'payload' : payload2,
      'muteHttpExceptions' : false
  };
  try {
      var response = UrlFetchApp.fetch(url2, fetchParameters2);
      } catch(e){
        Logger.log(e)
  }
}

RESULTING RECORD IN MONGODB:

  _id:60072da8c52278001791e22e
title:"42612001948065650"
en1:null
date:2021-01-19T19:06:16.052+00:00
__v:0
de1:null
de2:null
de3:null
de4:null
en2:"<div class="qw">
        <div class="qe" data-country="gq.svg"> <p cla..."
en3:null
en4:null
fr1:null
fr2:null
fr3:null
fr4:null

Here you can see how the PATCH request is overwriting the data in en1 to null, how can I make it only update en2, and skip the other values?



from Patch request from google sheets overriding previous post request

No comments:

Post a Comment