Google Sheets - basics of working with strings

1,543 views
Skip to first unread message

Anne Cutler

unread,
Feb 8, 2023, 3:48:33 PM2/8/23
to Google Apps Script Community

First time posting here. I'm an experienced coding, but it's been a long time since I've done any, and I'm starting back up with App Script, a new language for me. I'm trying to do some basic stuff with text found within cells in a Google sheets. I've gotten it to work well enough, but I think my code can be simplified and improved if I learn a little bit more about working with text strings in App Script.

This is a very simplified version of my function. My real function finds the page numbers given in a citation in one cell, and puts just those page numbers in another cell. For the purposes of this question, I've simplified it to retrieve the text from the current cell, remove the first blank space in the text, count the numbers at the beginning of the text, and then write just those numbers into the current cell. It does what it is supposed to do, and what I need it to do, but I have so many questions! Thank you!!

function myFunction() {

    var spreadsheet = SpreadsheetApp.getActive(); 
    var currentCell = spreadsheet.getCurrentCell().activate(); 
    var style = SpreadsheetApp.newTextStyle().setForegroundColor('#000000').build(); 
    var textString = currentCell.getRichTextValue().getText(); 
    var count = 0; 
    var char = textString.substring(count,count+1); 
    textString = textString.replace(" ",""); 

    while(char<10) { 
         count = count+1; 
         char = textString.substring(count,count+1); 
     } 
     var numbers = textString.substring(0,count);
     currentCell.setRichTextValue(SpreadsheetApp.newRichTextValue().
       setText(numbers) .setTextStyle(1, count, style).build());

};

  1. In retrieving my textString, is there a way to do it without using "getRichTextValue()"?

  2. In writing the new text (numbers), is there a way to do that without using "setRichTextValue()"? And to do it without specifying the style?

  3. In my while loop, I use char<10. This works, but I'm not sure why. char is a one character string, right? The character is a number, but I am thinking I shouldn't be able to compare with a number because it's a string? Also, it actually lets blank spaces through as well, so I know something is wrong. What can I do instead?

  4. How can I get the replace function to remove ALL the blank spaces in my textString?

Leah Janss Lafond

unread,
Feb 11, 2023, 3:27:40 PM2/11/23
to Google Apps Script Community
I'm newish to all this, but have been taking more from forums than giving, so I'll take a stab at this.  Hope it helps.

I think for 1, you can just write var textString = currentCell.getValue();

for 2, try currentCell.setValue(numbers)

For 3, perhaps try to convert your string into a char with parseInt() - while(parseInt(char) < 10)

For 4,  I think you need to add the global modifier, otherwise it just replaces the first match, so try  textString = textString.replace(/ /g, "");

Reply all
Reply to author
Forward
0 new messages