0
I hope you're well.

I'm using google sheets to help manage some data for my work. One of our programmers wrote a google apps script for me to log into our website, pull the data I need, and then dump it into google sheets. It works well, however sometimes it encounters and error and it fails to log in to our website. (please see the photo below)

Error I encounter

To fix the error, I need to delete the cell's contents, and then enter the function in that cell again. This forces the function to try again from scratch and it will load successfully.

What I cut and paste to fix it

What I need a way to automatically run all the functions again (something functionally the same as deleting it and entering it again manually).

I'm hoping for someway to create a button that I could press which would delete and re-enter the text in the relevant cells, or something like that. However, I would appreciate any solution that anyone can suggest.

I really appreciate it!

-edit: I should also mention that I'm using this function a lot in a large range of spreadsheets and they're all interconnected so when one gets an error, it kills the entire web of documents and I need to root around for which one failed and then copy its contents, delete it, then enter it again.

edit 2: Thank you fir your first response and sorry about posting images of the error I received. You've set me down the right path and I've combined this onEdit function for when I edit single sell to directly replace the text in that cell. When I use "foo" or something like that, it works and replaces the text in that sell with "foo". However, what I really need to replace it with is =ImportJSON("expertList", 1) but when I try i get an error: Syntax error: SyntaxError: Unexpected identifier line: 341 file: Code.gs Here's the code in question:

function onEdit(e) {
  if (e.range.getA1Notation() === 'A1') {
    refresher()
  }
}

function refresher() {
  var newValue = '=yourfunction()';
  var sheetName = "sheet name";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange("this is the cell you want to re-write");
  range.clearContent();
  SpreadsheetApp.flush();
  range.setValue(newValue);
}

Last Edit:

Thanks for the help! If anyone stumbles on this post in the future, the above code works to delete what's in cell, then enter it again into that same cell (thereby resetting the function)

Joshua1991
  • 1
  • 1
  • [Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) – helvete Jan 19 '21 at 13:46
  • Although I'm not sure whether I could correctly understand about your question, for example, is this thread useful for your situation? https://stackoverflow.com/q/61946438/7108653 – Tanaike Jan 19 '21 at 13:49
  • 1
    Thank you for your response, that set me down the right path but i'm now encountering a problem where I can't replace the cell with the text I want to enter. – Joshua1991 Jan 19 '21 at 15:02
  • use `var newValue = '=importJSON("expertList",1)';`. By the way, may I ask if this is the only function that you want to refresh? or you want to refresh all functions available in the sheet? – Ron M Jan 19 '21 at 15:07
  • That got it to work! Thank you so much. I actually have many many functions that I need to refresh (the website i'm importing the data from only supports 100 users per page, so I need to have a function for each page like "=importJSON("expertList",1)", and then one hundred cells below have "=importJSON("expertList",2)". My plan was to find a script that would help me re-enter the function and then replicate it as many times as was needed for each function that needs to be re-run. EDIT: Actually, I think I misunderstood you. They're all just the same function =importJSON. – Joshua1991 Jan 19 '21 at 15:17

0 Answers0