Home > front end >  How the best and fast way to send multiple data into google spreadsheet?
How the best and fast way to send multiple data into google spreadsheet?

Time:05-03

I want to send multiple data using chrome extension into Google spreadsheet. I tried using loop but sometimes it's replacing the data in the current process. I tried using async await but i guess i didn't understand how it works so it didn't give the result i wanted. I want to try doing a single batch input but i don't know how. Here's my Javascript code

async function sendmydata(name,num,tipe){
  const scriptURL = myurl;
  const form = new FormData();
    form.append("sheetname",name);
    form.append("id",num);
    form.append("type",tipe);

    await fetch(scriptURL, { method: 'POST', body: form})
      .then(response => console.log('skss'))
      .catch(error => console.log('fld'))
}

$('#sendbtn').click(function (e) { 
  e.preventDefault();
  var urlParams = new URLSearchParams(window.location.search);
  name = $('span.account-name').text();
  tipe = urlParams.get('type');
  vnum = $('span.orderid');
  stp = $('.status');
  jml = vnum.size();
  
  switch(tipe) {
    case 'completed':
      tipe = 'FINISH';
      break;
    case 'cancelled':
      tipe = 'FAILED';
      break;
  }

  switch (name) {
    case 'shop1':
      name = 'GH';
      break;
    case 'shop2':
      name = 'RH';
      break;
    case 'shop3':
      name = 'SL';
      break; 
  }

/* i also tried this but it's slow */
 /* (function myLoop(i) {
    setTimeout(function() {
      nresi = $('span.orderid').eq(i).text().substring(12);
      const form = new FormData();
      form.append("id",num);
      form.append("type",tipe);
      form.append("sheetname",name);
      
      //console.log(num ', ' tipe ', ' name);
      fetch(scriptURL, { method: 'POST', body: form})
      .then(response => console.log('skss'))
      .catch(error => console.log('fld'))

      if (i   < jml) myLoop(i);   //  decrement i and call myLoop again if i > 0
    }, 250)
  })(0); 
 */
  
  for (let i = 0; i < jml; i  ) {
    num = $('span.orderid').eq(i).text().substring(12);
    sendmydata(name,num,tipe);
  } 

and the code.gs

var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(e.parameter['sheetname'])

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow()   1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

Thank you !

CodePudding user response:

if you wan't to wait for each request to be finished before you send the next one, you have to await the fetch request. i.e.

$('#sendbtn').click(async function (e) { // async is added
...
  await sendmydata(name,num,tipe); // await is addded
})

CodePudding user response:

Solution:

Instead of making multiple post requests in a loop, add all your desired data in a single FormData and send all of it in a single post request.

Since name and tipe are always the same, and the only value that changes across iterations is num, I'd suggest adding all your nums into a single array, and send that array to Apps Script.

Code snippets:

For example, on the client-side, instead of this:

for (let i = 0; i < jml; i  ) {
  num = $('span.orderid').eq(i).text().substring(12);
  sendmydata(name,num,tipe);
}

Use this:

nums = $('span.orderid').map(() => $(this).text().substring(12)).get();
sendmydata(name, JSON.stringify(nums), tipe);

And on the server-side, instead of this:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues([newRow])

Do this:

var nums = JSON.parse(e.parameter["id"]);
var newRow = nums.map(function(num) {
  return headers.map(function(header) {
    switch (header) {
      case 'timestamp':
        return new Date();
      case 'id':
        return num;
      default:
        return e.parameter[header];
    }
  });
});
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
  • Related