0

Being helped by ChatGPT I've created this Google Sheets script, helps me detecting some information from the docx file linked on the first column.

It worked for months but, some weeks ago it suddenly stopped working, giving me this error

Exception: Document is missing (perhaps it was deleted, or maybe you don't have read access?)

I have access and the documents are not deleted. But it still won't work.

Do you have any Idea on how to solve it?

function FineFiller() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:A");
  var links = range.getValues();
  
  for (var i = 0; i < links.length; i++) {
    var doc = DocumentApp.openByUrl(links[i][0]);
    var text = doc.getBody().getText();
    
    var verbaleNum = text.substring(text.indexOf("Verbale n°: ") + 12, text.indexOf("Verbale n°: ") + 30);
    sheet.getRange(i + 2, 2).setValue(verbaleNum);
    
    var giorno = text.substring(text.indexOf("Il giorno ") + 10, text.indexOf("Il giorno ") + 20);
    sheet.getRange(i + 2, 3).setValue(giorno);
    
    var ore = text.substring(text.indexOf("alle ore ") + 9, text.indexOf("alle ore ") + 14);
    sheet.getRange(i + 2, 4).setValue(ore);
    
    var targa = text.substring(text.indexOf("targa ") + 6, text.indexOf("targa ") + 12);
    sheet.getRange(i + 2, 5).setValue(targa);
    
    var totale = text.substring(text.indexOf("per un totale di euro ") + 22, text.indexOf("per un totale di euro ") + 27);
    sheet.getRange(i + 2, 6).setValue(totale);
  }
}

I've tried to check if it was a problem related with the access to files, but it is not that.

EDIT: I'm adding this screenshot just to give a bit of graphic view of the results of the script before and after the error started to block my script from working. Being a company spreadsheet I can't share it and some data are hidden.

Screenshot

Emanuele Mario Marcari
  • 1
  • 1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Apr 11 '23 at 12:54
  • How many rows are in the sheet? What is the last row in Column A that contains a link? My guess is that originally there were as many links as rows, but somehow you now have more rows than links. If this is the case, then the solution is easy. – Tedinoz Apr 12 '23 at 05:01
  • hi there @Tedinoz, thank you for your comment. I'll post a screenshot so you can see the tab, unfortunately I can't share the spreadsheet to anybody outside the company I work for. Anyway I can assure you that nothing is now different than how it was before. It has the standard blank rows (1000 i guess). – Emanuele Mario Marcari Apr 12 '23 at 14:23
  • On a tangent: you range (`var range = sheet.getRange("A2:A");`) includes EVERY cell in the column, including blank cells. `links.length` will be > than the number of links so the script will end with an error something like `Exception: Invalid argument: url`. You can avoid this by setting an exact range by counting the contiguous rows containing values. This answer: [Determining the last row in a single column](https://stackoverflow.com/a/17637159/1330560) is one of my all-time favourites. – Tedinoz Apr 13 '23 at 01:46
  • My testing returns this error only when file is non-existent (not even in the "Bin"). Some questions: 1) Is this script being executed by a trigger (maybe time-driven)? 2) Please check the Executions Log for the exact ID (one or more?) causing the problem, and then check existence, sharing permissions and position in the list? Is it a recent addition, has it been edited recently, etc, etc? 3) Consider user permissions: read this answer to ["(perhaps it was deleted?)" Spreadsheet Error in Google Scripts](https://stackoverflow.com/a/23923179/1330560), most of which deals with user permissions. – Tedinoz Apr 13 '23 at 01:51
  • 1
    @Tedinoz I posted the answer, I've finally solved the issue. Thank you so much for your contribution, it was essential. It helped me to understand which was the problem and, after deleting all the rows except the last one (the one I needed to extract text from) it was working. Then I understood it was a range related issue and the script was detecting the first link, of an old document deleted after being 30 days into the bin. Having done another script with range rules, I confused it with this and since it was working I wasn't thinking it could create a future problem. I'm not a programmer :) – Emanuele Mario Marcari Apr 13 '23 at 20:39

1 Answers1

0

I've finally found the solution and, as always happens, it was quite simple. I don't know why (probably because the first files have automatically been deleted from bin), but after testing I discovered it was detecting from the first row instead of checking the first cell of column A which has a blank b to f range on the right.

So I solved implementing this code:

function FineFiller() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:F");
  var data = range.getValues();
  
  for (var i = 0; i < data.length; i++) {
    var link = data[i][0];
    var bValue = data[i][1];
    var cValue = data[i][2];
    var dValue = data[i][3];
    var eValue = data[i][4];
    var fValue = data[i][5];
    
    if (bValue === "" && cValue === "" && dValue === "" && eValue === "" && fValue === "") {
      var doc = DocumentApp.openByUrl(link);
      var text = doc.getBody().getText();


      var verbaleNum = text.substring(text.indexOf("Verbale n°: ") + 12, text.indexOf("Verbale n°: ") + 30);
      sheet.getRange(i + 2, 2).setValue(verbaleNum);


      var giorno = text.substring(text.indexOf("Il giorno ") + 10, text.indexOf("Il giorno ") + 20);
      sheet.getRange(i + 2, 3).setValue(giorno);


      var ore = text.substring(text.indexOf("alle ore ") + 9, text.indexOf("alle ore ") + 14);
      sheet.getRange(i + 2, 4).setValue(ore);


      var targa = text.substring(text.indexOf("targa ") + 6, text.indexOf("targa ") + 12);
      sheet.getRange(i + 2, 5).setValue(targa);


      var totale = text.substring(text.indexOf("per un totale di euro ") + 22, text.indexOf("per un totale di euro ") + 27);
      sheet.getRange(i + 2, 6).setValue(totale);
    }
  }
}
Emanuele Mario Marcari
  • 1
  • 1