Google sheets cells jumped all over the place

[Long Vũ Hoàng]

Hi! I am currently having a project that I can measure force from a punch and then send that data to google sheets from an ESP32, but there was a twist, the cell jumped all over the place and I couldn't figure this out. Here's a code in the App Script:

function doGet(e) {

  Logger.log(JSON.stringify(e));

  var result = 'Ok';

  if (e.parameter == 'undefined') {

    result = 'No Parameters';

  }

  else {

    for (var param in e.parameter) {

      Logger.log('In for loop, param=' + param);

      var value = stripQuotes(e.parameter[param]);

      Logger.log(param + ':' + e.parameter[param]);

      switch (param) {

        case 'forceData':

          var ssID = SpreadsheetApp.openById('SHEET_ID');

          var sheetName = ssID.getSheetByName('Sheet1');

          var newRow = sheetName.getLastRow() + 1;

          var rowData = [];

          var Curr_Date = new Date();

          rowData[1] = Curr_Date; // Date in column A

          var Curr_Time = Utilities.formatDate(Curr_Date, "Asia/Bangkok", 'HH:mm:ss');

          rowData[2] = Curr_Time; // Time in column B

          rowData[3] = value;

          result = 'OK';

          Logger.log(JSON.stringify(rowData));

          var newRange = sheetName.getRange(newRow, 1, 1, rowData.length);

          newRange.setValues([rowData]);

          break;

        case 'forceData2':

          var ssID = SpreadsheetApp.openById('1OGZENKSpxyGlRz33WbcSXLVmA7dXGoGw7JDxarpRGY4');

          var sheetName = ssID.getSheetByName('Sheet1');

          var newRow = sheetName.getLastRow() + 1;

          var rowData = [];

          rowData[4] = value;

          result = 'OK';

          Logger.log(JSON.stringify(rowData));

          var newRange = sheetName.getRange(newRow, 1, 1, rowData.length);

          newRange.setValues([rowData]);

          break;

        default:

          result = "unsupported parameter";

      }

    }

  }

  return ContentService.createTextOutput(result);

}

function stripQuotes(value) {

  return value.replace(/^["']|['"]$/g, "");

} 

And here's a picture of my problem and also a picture of what I need to do.

Cheers!

[Gilberto Junior]

I think you have two problems:

First, you're replicating the newRow definition in each case clause. If forceData runs first and define newRow as 6, when forceData2 runs, newRow will be 7, causing the columns D and E be in different rows. Try to put the first three lines of the case clauses outside the case statement;

Second, if cell A5 is not empty, the next row will be 6. If you want to populate columns B to E, but already have the column A populated, you have to do a function (findByNameInColumnA) to fetch the correct row, and set values of the range correctly.

Hope it helps!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ad909973-f6af-415a-b834-10ddd02c2ab6n%40googlegroups.com.