Find replace with a function

[Martin MULLAN]

Hi all,

I am trying to replicate the 'find and replace' functionality of Sheets with GAS.

I have a folder of Gsheets that I can loop through, but I can't figure out how to use apps script to edit the code of the functions themselves. 

For example, I need to update the Form ID in this formula:

=if(AB4=-2,iferror(query(Lookups!$AM$3:$AU,"select AR where AQ=1 AND AP = """&$E4&""" AND AS = """&$J4&""" ",0),hyperlink(arrayformula("https://docs.google.com/forms/d/e/qwertyuioplkjhgfdsfghjhgfdfgh/viewform?entry.2027592121="&concatenate($D4," ",$C4)&"&entry.268434257="&$E4&"&entry.108198148=1&entry.1369475611="&$J4),"Go Chat"))

This form url appears up to 1000 times in per sheet, 5 sheets per spreadsheet and there are 30 spreadsheets in a folder. I can do this manually but OMG I am wasting so much time opening each sheet and using the find/replace dialog. 

Can any of you help with the find replace code needed to solve this?

[Tanaike]

I understood your goal as follows.

You want to replace "qwertyuioplkjhgfdsfghjhgfdfgh" of =if(AB4=-2,iferror(query(Lookups!$AM$3:$AU,"select AR where AQ=1 AND AP = """&$E4&""" AND AS = """&$J4&""" ",0),hyperlink(arrayformula("https://docs.google.com/forms/d/e/qwertyuioplkjhgfdsfghjhgfdfgh/viewform?entry.2027592121="&concatenate($D4," ",$C4)&"&entry.268434257="&$E4&"&entry.108198148=1&entry.1369475611="&$J4),"Go Chat")) with other value. There are several Spreadsheets in a folder and each Spreadsheet has several sheets. You want to reflect this for all sheets of all Spreadsheets.

In this case, how about the following sample script?

function myFunction() {

  const oldId = "qwertyuioplkjhgfdsfghjhgfdfgh";

  const newId = "###"; // Please set the new value.

  const folderId = "###"; // Please set the folder ID.

  const spreadsheets = DriveApp.getFolderById(folderId).getFilesByType(MimeType.GOOGLE_SHEETS);

  while (spreadsheets.hasNext()) {

    const spreadsheet = spreadsheets.next();

    console.log(`Now processing at ${spreadsheet.getName()}`);

    SpreadsheetApp.open(spreadsheet).createTextFinder(oldId).matchFormulaText(true).replaceAllWith(newId);

  }

}

In this sample script, the Spreadsheet is retrieved using DriveApp and the formula texts of all sheets in a Spreadsheet are changed using TextFinder.

[Martin MULLAN]

Oh dear...there is a method for this and I didn't find it 🤦‍♀️

Thank you Tanaike for responding so thoroughly to my noobness!

Mart.

[Tanaike]

Welcome. I'm glad your issue was resolved.

[Martin MULLAN]

Hey Tanaike,

I guess the more challenging job would be to replace cell references that increment down the sheet e.g. replace this function:

=if(B4<>"",CONCATENATE(K4,": ",C4,", ",D4),"")

with this one:

=if(B4<>"",hyperlink(arrayformula("https://script.google.com/a/macros/school/s/scriptID/exec?Email="&E4),CONCATENATE(K4,": ",C4,", ",D4)),"")

from rows 4 to 30 i.e. row 5 has =if(B5<>"",CONCATENATE(K5,": ",C5,", ",D5),"") etc.

Would you run a loop incrementing for the row reference in the text to find and replace, or is there a nice method that I'm not aware of? 😖😜

Cheers, Mart.

On Monday, 4 October 2021 at 10:14:23 UTC+8 Tanaike wrote:

[Martin MULLAN]

So I did this:

function replaceTextReferencesInFunction() {

  const folderId = "asdfghjklkjhgfdwertyu"; // Please set the folder ID.

  const spreadsheets = DriveApp.getFolderById(folderId).getFilesByType(MimeType.GOOGLE_SHEETS);

    while (spreadsheets.hasNext()) {

    const spreadsheet = spreadsheets.next();

    for(r=4;r<31;++r){

      const oldFormula = "=if(B"+r+"<>\"\",CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+"),\"\")";

      const newFormula = "=if(B"+r+"<>\"\",hyperlink(arrayformula(\"https://script.google.com/a/macros/scriptURL/exec?Email=\"&E"+r+"),CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+")),\"\")";

      SpreadsheetApp.open(spreadsheet).createTextFinder(oldFormula).matchFormulaText(true).replaceAllWith(newFormula);

    }

  }

}

It works and seems pretty fast but...I can't help feeling that calling SpreadsheetApp.open every loop is inefficient.

Can this be made better by a proper programmer! LOL

Mart.

  

[Tanaike]

In that case, how about putting `SpreadsheetApp.open(spreadsheet)` out of the for loop? So, `const ss = SpreadsheetApp.open(spreadsheet)` is put out of the for loop, and `ss.createTextFinder(oldFormula)...` is put in the for loop.

[Martin MULLAN]

Well...that is basically the same thing right? Open the spreadsheet 27 times and make a change as opposed to open the spreadsheet once and make 27 changes!

M

[Tanaike]

In your script, `SpreadsheetApp.open(spreadsheet)` is put in the for loop. In this case, this script is called every loop in `for(r=4;r<31;++r){}`. But when `SpreadsheetApp.open(spreadsheet)` is put out of the for loop, this is called one time for each Spreadsheet.

[Martin MULLAN]

Sorry dude...I clearly don't understand how this code works then. You suggested rewriting it to ss.createTextFinder(oldFormula) inside the code, but doesn't that mean the ss is still calling the spreadsheet to open since const ss = SpreadsheetApp.open(spreadsheet)?

[Tanaike]

Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand "I clearly don't understand how this code works then. You suggested rewriting it to ss.createTextFinder(oldFormula) inside the code, but doesn't that mean the ss is still calling the spreadsheet to open since const ss = SpreadsheetApp.open(spreadsheet)?". Can I ask you about the detail of your current issue?

[Martin MULLAN]

Hi Tanaike,

I am not a programmer. I did not study programming so I do not know how the code does what it does.

I did this:

   for(r=4;r<31;++r){

      const oldFormula = "=if(B"+r+"<>\"\",CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+"),\"\")";

      const newFormula = "=if(B"+r+"<>\"\",hyperlink(arrayformula(\"https://script.google.com/a/macros/scriptURL/exec?Email=\"&E"+r+"),CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+")),\"\")";

      SpreadsheetApp.open(spreadsheet).createTextFinder(oldFormula).matchFormulaText(true).replaceAllWith(newFormula);

    }

You said to do this:

  const ss = SpreadsheetApp.open(spreadsheet);

   for(r=4;r<31;++r){

      const oldFormula = "=if(B"+r+"<>\"\",CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+"),\"\")";

      const newFormula = "=if(B"+r+"<>\"\",hyperlink(arrayformula(\"https://script.google.com/a/macros/scriptURL/exec?Email=\"&E"+r+"),CONCATENATE(K"+r+",\": \",C"+r+",\", \",D"+r+")),\"\")";

      ss.createTextFinder(oldFormula).matchFormulaText(true).replaceAllWith(newFormula);

    }

I do not understand why this is different or more efficient. If I call ss in the loop does this not call SpreadsheetApp.open every time the loop occurs?

[Tanaike]

Thank you for replying. `SpreadsheetApp.open(spreadsheet)` returns the Spreadsheet object. In the above script, this is run every loop. But the below script, that is not used in the loop.

[Martin MULLAN]

..."But the below script, that is not used in the loop." so...why bother with the ss constant? This is the part I don't understand!

[Clark Lind]

By placing ss outside the loop, you create a single instance, and then work on that instance 27 times. If it is inside the loop, you create and destroy it 27 times.  

Think about this: if you wanted to retrieve 6 beers (or sodas) for you and your friends, would you get up from your chair, walk to the kitchen, open the refrigerator grab a single beer, return and give it to someone, and then repeat 5 more times, or just once and pull out six at one time? From your friends' point of view, you are correct, they don't care if you run to the kitchen 6 times or once, they seem equivalent to them and the result is the same. You just worked less efficiently in the first case. 

I hope that doesn't make it more confusing. 

[frank 6000]

nice analogy, Clark

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6737be3f-f286-4069-852f-a204a20375cen%40googlegroups.com.

--

[Tanaike]

@Clark Thank you for your support. I apologize for my poor English skill.

[Clark Lind]

Tanaike my friend, there is no need to ever apologize for your English skill. You communicate very well, sometimes better than those who speak English as their first/native language! :) 

[Tanaike]

@Clark Thanks. I would like to learn from various situations and study more!

[Martin MULLAN]

Hi Clark,

Thanks for your response. I understand what you are getting at, but I still ask why bother with the ss? If, in your analogy, I have gone to the fridge and got the beers already, why when I hand the beers out to each of my friends do I bother to tell each of them that I went to the fridge to get them a beer?

Your analogy reads as: Go to fridge to get 27 beers. For each friend, give beer.

The apps script reads as:  Go to fridge to get 27 beers. For each friend "I went to the fridge" give beer.

Now, I accept that if that is the way the code has to be written, fine. It just seems unnecessary to add the ss if the code already knows I had done to the fridge and retrieved what I needed...which it what seem to saying is happening.

I appreciate you are probably rolling your eyes...and I apologise for being a numpty!

Mart.

[Clark Lind]

lol no worries. Keep in mind that the code is running on the Google servers, not in the browser. Maybe a different angle might help.

If you were working on your desktop/laptop and writing some code that manipulates rows in an Excel file instead of a G-sheet, would you want to open the file one time and make the 27 changes, or open it 27 times?

The reason you include the "ss" in the createTextFinder(), is because you need to reference the open file, not that you are reopening it. Does that help? They are not equivalent. 

If you had to send your fleet through a wormhole, you would only want to open it once, then send your fighters through it. You still have to reference it so they know which wormhole to fly through. A reference to the open wormhole is not the same as opening a new wormhole for each fighter...   ok ok sorry.. too many SciFi audio books lately.

[Martin MULLAN]

haha, scifi good.

After I posted to you earlier I was thinking that a reference to the 'fridge' made sense.

Thanks for 'engaging' (scifi pun!) me :)

Mart