This function is designed to search through a spreadsheet and extract the link from the third column based on the value in the first column
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var searchMenuEntries = [ {name: "Search in all documents", functionName: "search"}];
ss.addMenu("Search Document List", searchMenuEntries);
}
function lastValue(column) {
var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();
for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
return values[lastRow - 1];
}
function search() {
// Get the current spreadsheet and active sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var startRow = 2; // Starting row of data
var numRows = lastValue("A"); // Total rows to process
var dataRange = sheet.getRange(startRow, 1, numRows)
var data = dataRange.getValues();
for (i in data) {
var files = DocsList.find(data[i].toString());
for (var j = 0; j < files.length; j++) {
if (files[j].getType() == "other"){
urlBase = "https://docs.google.com/fileview?id=";
sheet.getRange(i, 3, 1, 1).setValue(urlBase + files[j].getId());
}
}
}
}
However, when executed, an error occurs:
Cannot convert d59f868312238f16bd8534f61c01dd0695512d38 in (class)
The string mentioned above is the final value in column A that I use to retrieve the link with DocsList.find
Any ideas on what might be causing this issue?
Thank you