Only search the first few characters of a cell

Casey J 0
Viewed 9

I am looking to take the first 10 characters instead of taking the whole cell data. What I am attempting to do is make a google form where the only submission field is an ID (Ex cfb1-12345, where cfb1 is the name of a tab in the sheet and cfb1-12345 is the full ID) so that my app script command can take the first x number of characters (in the examples case 4) and substitute it for the "records" sheet name. I am doing this now by having a separate field with the tab name the script specifically searches for.

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSht = ss.getSheetByName("Form");
  var recordsSht = ss.getSheetByName(formSht.getRange("B4").getValue());

As shown here, it currently gets this value from cell B4, but I would like to be able to take the first X number of characters to fill this field to find the correct sheet tab. Any help is appreciated as I am quite the beginner.

Also, if anyone knows how to delay google forms responses from updating a spreadsheet to leave a 15-second interval between submissions, I would very much appreciate it!

google-forms google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • You want to retrieve cfb1 from cfb1-12345 in a cell "B4" using Google Apps Script.

In this case, when your showing script is modified, how about the following modification?

From:

var recordsSht = ss.getSheetByName(formSht.getRange("B4").getValue());

To:

var recordsSht = ss.getSheetByName(formSht.getRange("B4").getValue().split("-")[0].trim());
  • By this, when cfb1-12345 is put in the cell "B4", formSht.getRange("B4").getValue().split("-")[0].trim() returns cfb1.

Note:

  • About Also, if anyone knows how to delay google forms responses from updating a spreadsheet to leave a 15-second interval between submissions, how about using Utilities.sleep(15000)? Ref
Tanaike0
Related