so don't crucify me badly if dumb question,
but i've been dabbling in google app scripts, uses within google sheets. when i've been using openbyid() if it's id might change regularly, i'll put @ top of script , add notes on how edit (for user hand sheet on to, or me reminder if i've not worked on bit , forget). if's pretty stangnant , not change, i'll declare var.
i thinking it's bit more user friendly have "settings" sheet within spreadsheet , user inputs settings. rather having go script editor edit things.
but via sheet, or script editor, single thing added or removed in wrong place can cause havock, space or /
at least "settings" sheet, can use data validation , regular expressions control , reject user inputs.
so guess question google sheets id's seem alien me, bar length, , wondering there way validate id's using regular expressions or something, rather checking id server side. :)
you can use openbyurl() in scripts, users can paste link.
but if must validate id:
ss = spreadsheetapp.getactivespreadsheet(); function validate(){ //"sheetref" name of cell enter id //this function takes string value of "sheetref" //and replaces validation result. var sheetrefcell = ss.getrangebyname("sheetref"); var sheetrefstring = sheetrefcell.getvalue(); var validationresult = '{' + sheetrefstring + '}'; try { validationresult = validationresult + ' \n $ references: \n [' + spreadsheetapp.openbyid(sheetrefstring).getname() + ']'; } catch (e) { validationresult = validationresult + ' \n $ invalid reference: \n [' + e + ']'; } { sheetrefcell.setvalue(validationresult); } } when run replace value entered in 'sheetref' (id) cell validation result message. recommend add time-based trigger contininuously revalidate id.
then can add cell spreadsheet extract validated id 'sheetref'. cell should contain formula:
= if (iferror( search( 'invalid' , sheetref ), 0 ) > 0, "invalid", iferror( regexextract( sheetref,"{(.*?)}" ), "undetermined" ) ) so cell above formula either display valid id or "invalid"/"undetermined".
