Getting last Row on Sheet with only hyperlinks/references

Question

I have a sheet where there is a list of the other sheets in the spreadsheets with a link to them. The link looks like that:

=HYPERLINK(\"https://docs.google.com/spreadsheets/d/"+ssID+"/edit#gid="+sheetID+"\";"+"\""+name+"\")"

Note that the first sheet only contains these hyperlinks and a few references(=Test!B3 for example) to the other sheets and no direct Values.

When i try to get the last row with getLastRow() it always returns 0, disregarding the amount of rows actually filled with links/references. The same occurs when i use the here proposed code:

function getFirstEmptyRow(sheet) {
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}

It also always returns 1. So i came to the conclusion that hyperlinks and references do not count as Cell Value. Is there any way to get the last row with references/hyperlinks in it, getting the last row of the sheet that is actually used? The whole code of the function:

function restoreSheet(form)
{
    try
    {
        var name = form.pRestore;
        if (name != "")
        {
            var archive = SpreadsheetApp.openById('ID');
            var sheet = archive.getSheetByName(name);
            var list = archive.getSheetByName('list');
            var values = list.getDataRange().getValues();
            var destination = SpreadsheetApp.openById('ID');
            var overview = destination.getSheetByName('cover sheet');
            for (var i = 0; i < list.getLastRow(); i++)
            {
                if (values[i][0] == name)
                {
                    var rowIndex = i + 1;
                }
                else if (i >= overview.getLastRow())
                {
                    return false;
                }

            }
            //erasing stuff on the archives
            sheet.copyTo(destination).setName(name);
            archive.deleteSheet(sheet);
            list.deleteRow(rowIndex);
            list.sort(1);
            // adding stuff to the overview
            var rowindex = getFirstEmptyRow(overview);
            var ssID = ss.getId(); //ID of the spreadsheet for link creation
            var oldSheet = ss.getSheetByName(name);
            if (oldSheet != null)
            {
                var sheetID = oldSheet.getSheetId(); //ID of the sheet for link creation
            }
            else
            {
                Browser.msgBox(oldName + " was not found");
            }
            var link = "=HYPERLINK(\"https://docs.google.com/spreadsheets/d/" + ssID + "/edit#gid=" + sheetID + "\";" + "\"" + name + "\")";
            overview.getRange(rowIndex + 1, 3).setValue(link); //Project Name (so that ppl who can only see the sheet can get to the right sheet
            overview.getRange(rowIndex + 1, 4).setValue("='" + name + "'!B3"); //Project Owner
            overview.getRange(rowIndex + 1, 5).setValue("='" + name + "'!H1"); //Group
            overview.getRange(rowIndex + 1, 6).setValue("='" + name + "'!N1"); //Tags
            overview.getRange(rowIndex + 1, 7).setValue("='" + name + "'!M11"); //Wanted
            overview.getRange(rowIndex + 1, 8).setValue("='" + name + "'!F1"); //Status  
        }
        else
        {
            Browser.msgBox("Not all required fields filled");
            return false;
        }
    }
    catch (e)
    {
        Browser.msgBox(e);
    }
}
/**
* Function to get the first completely empty row
*/
function getFirstEmptyRow(sheet) {
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}

Have these hyperlinks been made by the script or pasted manually? — Max Makhrov, Sep 29 '16 at 12:43
The Hyperlinks have been created by the script, as well as the references. But by a different function. There is also a function to rename a sheet. This function compares the hyperlink(getFormular works) and then creates a new one with the new sheets name. That works, just getting the last row does not. — , Sep 29 '16 at 12:45
I don't know the exact reason, to solve it, need all your code to try. You may use this: `SpreadsheetApp.flush();` before `Logger.log(sheet.getLastRow());` — Max Makhrov, Sep 29 '16 at 12:57

score 0 · Answer 1 · answered Sep 29 '16 at 13:13

0

flush()

Applies all pending Spreadsheet changes. Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

SpreadsheetApp.flush(); before Logger.log(sheet.getLastRow()); can help

answered Sep 29 '16 at 13:13

Max Makhrov

17,309
5
55
81

Ok. the solution did not work, gonna provide my code. – Sep 29 '16 at 13:21
Yeah, sorry about the confusion, flush() seemed to solve it, but that was just a coincidence and me to happy to doublecheck. – Sep 29 '16 at 13:27

score 0 · Accepted Answer · 2016-09-29T13:41:22.063

0

I found the solution: As can be seen in the code in the beginning, i chose the same variable name for 2 different sheet row indexes. That caused the script to take the wrong one. I feel pretty dumb for not seeing it :/.

edited Sep 29 '16 at 13:41

answered Sep 29 '16 at 13:31

Getting last Row on Sheet with only hyperlinks/references

2 Answers2