/** Ruralbook bridge. Configure SPREADSHEET_ID and RURALBOOK_TOKEN in Script Properties. */ function doPost(e) { const result = value => ContentService.createTextOutput(JSON.stringify(value)).setMimeType(ContentService.MimeType.JSON); try { const body = JSON.parse(e.postData.contents), props = PropertiesService.getScriptProperties(); const secret = props.getProperty('RURALBOOK_TOKEN'), spreadsheetId = props.getProperty('SPREADSHEET_ID'); if (!secret || secret.length < 32 || body.token !== secret || body.spreadsheetId !== spreadsheetId) return result({ok:false,error:'Acesso recusado'}); const book = SpreadsheetApp.openById(spreadsheetId); if (body.action === 'ping') return result({ok:true,spreadsheetId}); if (body.action !== 'sync' || !Array.isArray(body.updates) || body.updates.length > 20) return result({ok:false,error:'Pedido inválido'}); const lock = LockService.getScriptLock();lock.waitLock(20000); try { const sheet = book.getSheetByName('Lotes');if (!sheet) throw Error('Aba Lotes ausente'); let headers = sheet.getRange(1,1,1,Math.max(1,sheet.getLastColumn())).getValues()[0].map(x=>String(x).trim().toUpperCase()); if (!headers.includes('ID_LOTE')) throw Error('ID_LOTE ausente'); const wanted = [...new Set(body.updates.flatMap(row=>Object.keys(row)))];wanted.push('RB_ATUALIZADO_EM'); for (const name of wanted) if (!headers.includes(name)) {headers.push(name);sheet.getRange(1,headers.length).setValue(name);} for (const record of body.updates) { const id = String(record.ID_LOTE || '');if (!id) throw Error('ID inválido'); const idCol = headers.indexOf('ID_LOTE')+1; const matches = sheet.getLastRow()>1?sheet.getRange(2,idCol,sheet.getLastRow()-1,1).getDisplayValues().flat().map((value,index)=>({value,index})).filter(row=>row.value===id):[]; if (matches.length>1) throw Error('ID_LOTE duplicado'); const rowNo=matches.length?matches[0].index+2:Math.max(2,sheet.getLastRow()+1); const range=sheet.getRange(rowNo,1,1,headers.length), values=range.getValues()[0], formulas=range.getFormulas()[0]; const output=values.map((value,index)=>formulas[index]||value); for (const [key,value] of Object.entries(record)) {if (!headers.includes(key))continue;output[headers.indexOf(key)]=typeof value==='string'&&/^[=+@-]/.test(value)?"'"+value:value;} output[headers.indexOf('RB_ATUALIZADO_EM')]=new Date().toISOString();range.setValues([output]); let mediaSheet=book.getSheetByName('Midias');if(!mediaSheet)mediaSheet=book.insertSheet('Midias');if(!mediaSheet.getLastRow())mediaSheet.getRange(1,1,1,5).setValues([['ID_LOTE','ORDEM','TIPO','URL','LEGENDA']]); if(mediaSheet.getLastRow()>1){const existing=mediaSheet.getRange(2,1,mediaSheet.getLastRow()-1,1).getDisplayValues().flat();for(let i=existing.length-1;i>=0;i--)if(existing[i]===id)mediaSheet.deleteRow(i+2);} const urls=String(record.MIDIAS_URLS||'').split(String.fromCharCode(10)).map(x=>x.trim()).filter(Boolean);if(urls.length)mediaSheet.getRange(mediaSheet.getLastRow()+1,1,urls.length,5).setValues(urls.map((url,i)=>[id,i+1,url.includes('youtu')?'YOUTUBE':'IMAGEM',url,''])); } let config=book.getSheetByName('Leilao');if(!config)config=book.insertSheet('Leilao'); if(!config.getLastRow())config.getRange(1,1,1,2).setValues([['CAMPO','VALOR']]); for(const [key,value] of Object.entries(body.config||{})){const keys=config.getLastRow()>1?config.getRange(2,1,config.getLastRow()-1,1).getValues().flat():[];const index=keys.indexOf(key);config.getRange(index>=0?index+2:config.getLastRow()+1,1,1,2).setValues([[key,typeof value==='string'&&/^[=+@-]/.test(value)?"'"+value:value]]);} SpreadsheetApp.flush();return result({ok:true,updated:body.updates.length}); } finally {lock.releaseLock();} } catch(error) {return result({ok:false,error:'Confira a autorização e a estrutura da planilha.'});} }