0

Put simply, I am working in Google Apps script, and I have an array value. I want to insert the array value into a sheet cell and the end result being the cell is set to plain text.

Everything about my code works, but this line refuses to result in a plain text output:

function minimal()
{ 
   const allFormResponses = fetchFormResponses();  //loads numbers from form output into a 2D array
   var sheet = SpreadsheetApp.getActive().getSheetByName("All");  
   var lastRow = sheet.getLastRow()+1;
   for(var d = 0; d < allFormResponses.length; d++) 
   {
      var lastRow = sheet.getLastRow()+1;
      for (var e = 0, col = 1; e < allFormResponses[d].length; e++, col++)
      {
         sheet.getRange(lastRow,col).setValue(JSON.stringify(allFormResponses[d][e].itemResponse.toString())).setNumberFormat("@");     
      }
   } 
}

I have also tried just formatting the entire column after each value is added and that doesn't work either.

Feeling stumped and I feel like it's probably something dumb I am missing.

Dave
  • 87
  • 1
  • 11
  • Please add a [mcve]. – Rubén Jul 27 '22 at 17:24
  • why are you setting the values to string first and after that changing the format to number? `itemResponse.toString()).setNumberFormat("@");`. If I remember correctly, [setNumberFormat(numberFormat)](https://developers.google.com/apps-script/reference/spreadsheet/range#setNumberFormat(String)) will *Sets the number or date format to the given formatting string.* so the result will not be plain text anymore but number format. – Giselle Valladares Jul 27 '22 at 17:29
  • However, it will be good to have more information or an example. – Giselle Valladares Jul 27 '22 at 17:35
  • @GiselleValladares The .toString() was just desperation, trying to make the output conform. I'd read on SO and elsewhere that .setNumberFormat("@") = "Plain text" but that doesn't seem to be the case. – Dave Jul 27 '22 at 19:11
  • I think the `.toString` is okay since this will change the array to plain text, but the `.setNumberFormat` will change any string format to number format. So it is usually used like this `setNumberFormat(newNumberFormat);`, and `.toString()` method should return a string representation. You can review more [here](https://www.techonthenet.com/js/string_tostring.php). Because of this I will say that `.toString() = "Plain Text"` – Giselle Valladares Jul 27 '22 at 19:23
  • 1
    @Dave, are you getting the values as JSON, or is it returned as an "Array"? Because I was reviewing the documentation of FormApp, and I don't see that it returns a JSON but just arrays. – Giselle Valladares Jul 27 '22 at 19:57
  • @Dave, have you try using `.setNumberFormat('@STRING@');` in the cell fist. Like adding in the line after the `for` -> `sheet.getRange(lastRow,col).setNumberFormat('@STRING@');` and after that Rubén suggestion: `sheet.getRange(lastRow,col).setValue(`'${allFormResponses[d][e].itemResponse}`);`? – Giselle Valladares Jul 29 '22 at 17:35

1 Answers1

0

Replace

sheet.getRange(lastRow,col).setValue(allFormResponses[d][e].itemResponse).setNumberFormat("@");

by

sheet.getRange(lastRow,col).setValue(`'${allFormResponses[d][e].itemResponse}`);

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Tried it, didn't work. Cell is still set to "Automatic" in Sheets, causing the number to be...a number. – Dave Jul 27 '22 at 19:51