Space written to empty Sheets cells

Hi all,
We have noticed that form data written from Collect to Google Sheets since between 5th and 8th June places a space in each empty cell. I have tried several searches on the topic and can find no related content. Only noticed it because some of the derivative fields started misbehaving.
Have I missed this topic in the migration? Can anyone else witness that behaviour?
My first visit to the new Forum. Thanks to the team who built this.
Best wishes, Chris

Hi,
It has been implemented to fix this issue https://github.com/opendatakit/collect/issues/931
is it a problem for you?

Thanks for the speedy response. Good to know what it is. I am sure we can
work around it with a TRIM statement or the Sheets equivalent.
We were concatenating two fields to catch a manual response in the event of
a QR code not reading and then pivoting off the last character but where
there was no manual response the pivot missed the item ie read the space.

Best wishes,
Chris

1 Like

We should have thought more carefully about the implications for downstream analysis. Using something like a trim sounds good and we should make sure to document this quirky behavior. I've filed a documentation issue at https://github.com/opendatakit/docs/issues/14.

1 Like

Hello all, this is an issue I am currently experiencing and would like to find out if this has been resolved? I am using array formulas but they are not working due to the empty space being auto-generated in blank cells. Thank you!

Hi @Kimberly_Bloch
welcome to the forum! Please introduce yourself here!

nothing has changed and it works in the same way.

Hi there. The spaces are becoming a major issue for my work flow :frowning: . Here is a video https://www.loom.com/share/e0bf74ad233540dfabaf39499b19d5dd

does anyone know of a script or can help me write a script that finds and replaces blank spaces every few seconds? To clear and fix my arrays? Greatly appreciated.

Hi @Kimberly_Bloch
I think such a function should work:

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    var values = sheet.getDataRange().getValues();
  
    for(var row in values){
       var replaced_values = values[row].map(function(original_value){
       return original_value.toString().replace(" ", "");
    });

    values[row] = replaced_values;
  }

  sheet.getDataRange().setValues(values);
}

To add it you need to open your spreadsheet and click Tools -> Script editor.
In order to make it automated you can play with Triggers (in script editor click that clock icon). There you can create a new trigger and choose on change option that means every time your spreadsheet is updated the function will be triggered.

Please test it maybe on a separate spreadsheet to make sure you won't loose your data since it's not something I use on a daily basis I just came up with this idea.

PS It's been a while since we added this fix/workaround I'll need to check because maybe we no longer need it.

1 Like