Ignore empty cell values which have formula in it

Question

Hi I'm Using JDBC connector to insert some data from google sheet to mysql

Can anyone help me on how to ignore empty cell values which have formula in it ?

i'm just getting the data in my import data sheet from another sheet with simple ='Sheet1'!C1 formula

I have tried

var newdata = [i for each (i in data)if (isNaN(i))];

and

var newdata = data.filter(String);

But not able to fix the problem ( Might be the join(',') causing the issue after filtering)

Below Log details for your reference - Thank you for any help or guidance you can provide. Let me know if you require any more details

[19-07-15 09:22:29:522 PDT] INSERT INTO CymaxVendorReachout2(UTID,Process,BatchNo,BatchName,ReceivedDate,SKUCount,Status,VMTTicket,AssignedTo,Comments,querytype,EmailSentDate,FolUpNum,Folupfrom,FolUpDate,NextFolUpDate,Note) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[19-07-15 09:22:29:525 PDT] [MER-8903-2019-07-29, Full Merch, MER-8903, Darlee - 903, 2019-07-24, 158, Invalid, No, Onkar, , , 2019-07-29, 1, Vendor, 2019-07-31, 2019-08-02, ]
[19-07-15 09:22:29:530 PDT] [MER-8904-2019-07-30, Full Merch, MER-8904, Darlee - 904, 2019-07-25, 159, Invalid, No, Onkar, , , 2019-07-30, 1, Vendor, 2019-08-01, 2019-08-05, ]
[19-07-15 09:22:29:536 PDT] [MER-8905-2019-07-31, Full Merch, MER-8905, Darlee - 905, 2019-07-26, 160, Invalid, No, Onkar, , , 2019-07-31, 1, Vendor, 2019-08-02, 2019-08-05, ]
[19-07-15 09:22:30:038 PDT] [, , , , , , , , , , , , , , , , ]
[19-07-15 09:22:30:044 PDT] [, , , , , , , , , , , , , , , , ]
[19-07-15 09:22:30:050 PDT] [, , , , , , , , , , , , , , , , ]
[19-07-15 09:22:30:055 PDT] [, , , , , , , , , , , , , , , , ]
[19-07-15 09:22:55:536 PDT] Data truncation: Incorrect date value: '' for column 'ReceivedDate' at row 1

Full google app script i'm running

function writeToTable() {
  var ss = SpreadsheetApp.getActive();
  var sheetDetails = ss.getSheetByName('Details');
  var sheetData = ss.getSheetByName('Data');
  var sheetImportData = ss.getSheetByName('Import Data');

  var host = sheetDetails.getRange("B1").getValue();
  var databaseName = sheetDetails.getRange("B2").getValue();
  var userName = sheetDetails.getRange("B3").getValue();
  var password = sheetDetails.getRange("B4").getValue();
  var port = sheetDetails.getRange("B5").getValue();
  var tableName = sheetDetails.getRange("B6").getValue();

  var url = 'jdbc:mysql://'+host+':'+port+'/'+databaseName;

  Logger.log(url);
  var sql = 'SELECT * FROM ' + tableName;

  var data = getData()
  var headerString = data[1]
  data = data[0]
  try{
    var start = new Date();
    var conn = Jdbc.getConnection(url, userName, password);
    conn.setAutoCommit(false);

    var sql = 'INSERT INTO ' + tableName + headerString;
    Logger.log(sql);
    var stmt = conn.prepareStatement(sql);


    var dateColumns = []; // Add the index to this array for date type column
    for (var r in data) {
      Logger.log(data[r]);
      for (var c in data[r]){
        if (dateColumns.indexOf(parseInt(c) + 1) >= 0){ 
          var date = Utilities.formatDate(data[r][c], Session.getScriptTimeZone(),  "yyyy-MM-dd HH:mm:ss");
          stmt.setObject(parseInt(c) + 1, date)
        }else{
          stmt.setString(parseInt(c) + 1, data[r][c]);
        }
      }
      stmt.addBatch();
    }

    var batch = stmt.executeBatch();
    conn.commit();
    conn.close();

    var end = new Date();
    Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
    SpreadsheetApp.getActive().toast('Time elapsed: %sms for %s rows.', end - start, batch.length);
  }catch(err){
    SpreadsheetApp.getActive().toast(err.message);
    Logger.log(err.message);
  } 
}
function getData(){
  //(UTID, Process, BatchNo, BatchName, ReceivedDate, SKUCount, Status, VMTTicket, AssignedTo, Comments, querytype, EmailSentDate, FolUpNum, Folupfrom, FolUpDate, NextFolUpDate, Note ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
  var ss = SpreadsheetApp.getActive();
  var sheetImportData = ss.getSheetByName('Import Data');
  var data = sheetImportData.getDataRange().getValues();
  var header = data[0];
  var headerString = header.join(',')

  headerString = '(' + headerString + ') values ('

  var questions = Array(header.length + 1).join('?').split('')

  headerString = headerString + questions.join(',') + ')'
  Logger.log(headerString)
  Logger.log(data.slice(1))
  return [data.slice(1), headerString]
}

Answer 1

• For the data range of the sheet of Import Data, you want to retrieve the values by removing the rows that the values of all cells ar empty, while the cells have the formula.

From your question and replying, I could understand like above. In order to achieve this, please modify the script of getData() as follows.

Modified script:

Please add the following script below var data = sheetImportData.getDataRange().getValues(); in the function of getData().

var newData = data.filter(function(row) {return row.filter(String).length > 0});

or

data = data.filter(function(row) {return row.filter(String).length > 0});

Reference:

• filter()

Answer 2

Edit 2: Tanaike's answer is more succinct:

https://stackoverflow.com/a/57048404/8240995

Mine goes about it by finding and deleting elements from an array, which might be useful for different projects.

Google Apps Script portion

If you're interested in only fetching the cell's visible value, i.e. as you'd see when you open the spreadsheet, you can replace

.getValue()

with

.getDisplayValue()

For cells that have a formula with a blank result, .getDisplayValue() will return an empty string. It also carries whatever formatting is applied within the spreadsheet, such as date and time formatting.

Edit - new information

I'd edit your script to ignore blank cell display values in the getData() function:

     function getData(){
          //(UTID, Process, BatchNo, BatchName, ReceivedDate, SKUCount, Status, VMTTicket, AssignedTo, Comments, querytype, EmailSentDate, FolUpNum, Folupfrom, FolUpDate, NextFolUpDate, Note ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
          var ss = SpreadsheetApp.getActive();
          var sheetImportData = ss.getSheetByName('Import Data');
          // replacing this to fetch the display values var data = sheetImportData.getDataRange().getValues();
          var data = sheetImportData.getDataRange().getDisplayValues();
          var header = data[0];
          var headerString = header.join(',')

          headerString = '(' + headerString + ') values ('

          var questions = Array(header.length + 1).join('?').split('')

          headerString = headerString + questions.join(',') + ')'
          Logger.log(headerString)
    // now the header's sorted, we can trim the data to exclude any blank entries
          var newData = data.slice(1);
    for (var i = 0; i < newData.length; i++){
    if (newData[i] === ''){
      newData.splice([i], 1);
      i = i - 1;// set the counter back one to catch consecutive blanks
    }
    }
          Logger.log(newData)
          return [newData, headerString]
        }

Judging by the code it looks like all the data is in a single row.

I haven't inspected the relationship with the header row. If you have column headers which are meant to have data line up, my code will be problematic. But since you want to remove all blanks, I'm assuming there isn't any column association like this.